Private Temporary Tables Oracle Database 18C

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.