by Oracle 18c introduced, Private temporary tables concept which 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 “CREATE PRIVATE TEMPORARY TABLE” statement to create a private temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific.
Here is my explanation on Oracle Live SQL.
This statement creates a private temporary table that is transaction specific:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_MyData_trans (id number, Name varchar2(30)) ON COMMIT DROP DEFINITION; Table created. insert into ORA$PTT_MyData_trans values (1, 'john'); 1 row(s) inserted. # Check data before commit. select count(*) from ORA$PTT_MyData_trans; COUNT(*) ---------- 1 Commit; Statement processed. # Check data after commit. # Table is available until your transaction is not committed. select count(*) from ORA$PTT_MyData_trans; ORA-00942: table or view does not exist # Table is fully private and even does not exist in data dictionary. select object_name from dba_objects where object_name like 'ORA$PTT_MyData_trans'; ORA-00942: table or view does not exist |
We can overwrite this default behaviour with below command and can preserve the definition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_MyData_session (id Number, name varchar2(30)) ON COMMIT PRESERVE DEFINITION ; Table created. insert into ORA$PTT_MyData_session values(1,'mandy'); 1 row(s) inserted. insert into ORA$PTT_MyData_session values(1,'john'); 1 row(s) inserted. select count(*) from ORA$PTT_MyData_session; COUNT(*) ---------- 2 commit; Statement processed. # Table is there after commit select count(*) from ORA$PTT_MyData_session; COUNT(*) ---------- 2 # Table does not exist in data dictionary. Its available until your session. select object_name from dba_objects where object_name like 'ORA$PTT_MyData_session'; ORA-00942: table or view does not exist # You can't gather statistics as this table won't exist in data dictionary. exec dbms_stats.gather_table_stats('','ORA$PTT_MyData_session'); ORA-20000: Unable to analyze TABLE "SQL_KKSIZLTMGFVAJWYAQWESZLBPX"."ORA$PTT_MYDATA_SESSION", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094 ORA-06512: at "SYS.DBMS_STATS", line 38371 ORA-06512: at "SYS.DBMS_STATS", line 38530 ORA-06512: at "SYS.DBMS_STATS", line 39076 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SQL", line 1721 |
Private temporary tables must be prefixed with “ORA$PTT_” and this can’t be used for regular table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE PRIVATE TEMPORARY TABLE MyData_session (id Number, name varchar2(30)) ON COMMIT PRESERVE DEFINITION ; ORA-00903: invalid table name # Normal table with pre-fix CREATE TABLE ORA$PTT_MyData_session (id Number, name varchar2(30)) ON COMMIT PRESERVE DEFINITION ; ORA-00903: invalid table name |
Private temporary tables are useful in the following situations:
- When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session.
- When a session is maintained indefinitely and must create different temporary tables for different transactions.
- When the creation of a temporary table must not start a new transaction or commit an existing transaction.
- When different sessions of the same user must use the same name for a temporary table.
- When a temporary table is required for a read-only database.
Mandy!!!
Leave a Reply