New type of temporary tables appeared in 18C called Private Temporary Tables. They 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.
CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT DROP DEFINITION or CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT PRESERVE DEFINITION
DROP DEFINITION |
This creates a private temporary table that is transaction specific. All data in the table is lost, and the table is dropped at the end of transaction. |
PRESERVE DEFINITION |
This creates a private temporary table that is session specific. All data in the table is lost, and the table is dropped at the end of the session that created the table. |
Private temporary table name must start with prefix defined by instance parameter private_temp_table_prefix
SQL>show parameter private NAME TYPE VALUE -------------------------- ------- --------- private_temp_table_prefix string ORA$PTT_
Following statement raises exception because of wrong prefix in table name
CREATE PRIVATE TEMPORARY TABLE test_tbl_session ( time_id DATE, amount_sold NUMBER(10,2) ) ON COMMIT PRESERVE DEFINITION; CREATE PRIVATE TEMPORARY TABLE test_tbl_session * ERROR at line 1: ORA-00903: invalid table name
Following example shows howto create private temporary table that exists till first COMMIT in session. Important note – there are dedicated views that shows detailed information about private temporary tables DBA|USER_PRIVATE_TEMP_TABLES.
SQL> connect tomasz/tomasz CREATE PRIVATE TEMPORARY TABLE ORA$PTT_test_tbl_session ( time_id DATE, amount_sold NUMBER(10,2) ) ON COMMIT DROP DEFINITION; SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables; SID SERIAL# OWNER TABLE_NAME DURATION --- ------- ------ ------------------------ ----------- 132 55609 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION
What is interesting you can create the same table name for the same user in another concurrent session
SQL> connect tomasz/tomasz CREATE PRIVATE TEMPORARY TABLE ORA$PTT_test_tbl_session ( time_id DATE, amount_sold NUMBER(10,2) ) ON COMMIT DROP DEFINITION; SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables; SID SERIAL# OWNER TABLE_NAME DURATION --- ------- ------ ------------------------ ----------- 11 37471 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION SELECT sid, serial#, owner, table_name, duration FROM dba_private_temp_tables; SID SERIAL# OWNER TABLE_NAME DURATION --- ------- ------ ------------------------ ----------- 132 55609 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION 11 37471 TOMASZ ORA$PTT_TEST_TBL_SESSION TRANSACTION
Time to load and commit data
--load data INSERT INTO ORA$PTT_test_tbl_session VALUES(sysdate, 10); 1 row created. --check data SELECT count(*) FROM ORA$PTT_test_tbl_session; COUNT(*) ---------- 1 --commit data and check table is dropped COMMIT; Commit complete. SELECT count(*) FROM ORA$PTT_test_tbl_session; ORA-00942: table or view does not exist
Another type of private temporary table is ON COMMIT PRESERVE DEFINITION. As long as user stays connected table exists.
After re connection table is gone.
SQL> connect tomasz/tomasz CREATE PRIVATE TEMPORARY TABLE ORA$PTT_test_tbl_commit ( time_id DATE, amount_sold NUMBER(10,2) ) ON COMMIT PRESERVE DEFINITION; INSERT INTO ORA$PTT_test_tbl_commit VALUES(sysdate, 10); 1 row created. COMMIT; Commit complete. INSERT INTO ORA$PTT_test_tbl_commit VALUES(sysdate, 10); 1 row created. COMMIT; Commit complete. SELECT sid, serial#, owner, table_name, duration FROM dba_private_temp_tables WHERE table_name = 'ORA$PTT_TEST_TBL_COMMIT'; SID SERIAL# OWNER TABLE_NAME DURATION --- ------- ------ ------------------------ ----------- 11 37471 TOMASZ ORA$PTT_TEST_TBL_COMMIT SESSION SQL> connect tomasz/tomasz SELECT sid, serial#, owner, table_name, duration FROM dba_private_temp_tables WHERE table_name = 'ORA$PTT_TEST_TBL_COMMIT'; no rows selected
Have a fun 🙂
Tomasz