18c new feature
New Feature Private Temporary Tables
What are private temporary tables?
Private temporary tables are temporary database objects that are dropped at the end of a transaction or session.
Private temporary tables are stored in memory and each one is visible only to the session that created it.The metadata and content of a private temporary table is visible only within the session that created it.
How to create private temporary tables?
Below syntax can be used to create private temporary table
SQL> conn c##test
Connected.
SQL> CREATE PRIVATE TEMPORARY TABLE ORA$ptt_session
(time_id DATE,
amount_sold NUMBER(10,2))
ON COMMIT PRESERVE DEFINITION; 2 3 4
Table created.
SQL> show user
USER is “C##TEST”
SQL> select table_name
from user_tables
where TEMPORARY = ‘Y’ 2 3
4 ;
TABLE_NAME
——————————————————————————–
MY_TEMP_TABLE =============> ORA$ptt_session is not displayed
SQL> select dbms_metadata.get_ddl(‘TABLE’,’ORA$ptt_session’) from dual;
ERROR:
ORA-31603: object “ORA$ptt_session” of type TABLE not found in schema “C##TEST”
ORA-06512: at “SYS.DBMS_METADATA”, line 6681
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_METADATA”, line 2582
ORA-06512: at “SYS.DBMS_METADATA”, line 2821
ORA-06512: at “SYS.DBMS_METADATA”, line 3602
ORA-06512: at “SYS.DBMS_METADATA”, line 4960
ORA-06512: at “SYS.DBMS_METADATA”, line 5288
ORA-06512: at “SYS.DBMS_METADATA”, line 6652
ORA-06512: at “SYS.DBMS_METADATA”, line 9672
ORA-06512: at line 1
no rows selected
SQL> select dbms_metadata.get_ddl(‘TABLE’,’ORA$PTT_SESISSION’) from dual;
ERROR:
ORA-31603: object “ORA$PTT_SESISSION” of type TABLE not found in schema
“C##TEST”
ORA-06512: at “SYS.DBMS_METADATA”, line 6681
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_METADATA”, line 2582
ORA-06512: at “SYS.DBMS_METADATA”, line 2821
ORA-06512: at “SYS.DBMS_METADATA”, line 3602
ORA-06512: at “SYS.DBMS_METADATA”, line 4960
ORA-06512: at “SYS.DBMS_METADATA”, line 5288
ORA-06512: at “SYS.DBMS_METADATA”, line 6652
ORA-06512: at “SYS.DBMS_METADATA”, line 9672
ORA-06512: at line 1
SQL> insert into ORA$ptt_session values(sysdate,1000);
1 row created.
SQL> commit;
Commit complete.
Open new session as the same user.
sqlplus c##test
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Development
Version 18.1.0.0.0
SQL> insert into ORA$ptt_session values(sysdate,1000);
insert into ORA$ptt_session values(sysdate,1000)
*
ERROR at line 1:
ORA-00942: table or view does not exist <===============This indicates the existence of private temporary table is only for the session which created
Names of private temporary tables must be prefixed according to the initialization parameter private_temp_table_prefix.Default value is ORA$PTT_.
SQL> show parameter private_temp_table_prefix
NAME TYPE VALUE
———————————— ———– ——————————
private_temp_table_prefix string ORA$PTT_
This parameter is not dynamic and value can be changed as
SQL> alter system set private_temp_table_prefix=’ORA$MINE’ scope=spfile;
System altered.
Related views:
DBA_PRIVATE_TEMP_TABLES
CDB_PRIVATE_TEMP_TABLES
WikiDBA 