Oracle 18c – Private Temporary Tables

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:

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

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(*)

----------

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.

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

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(*)

----------

commit;

Statement processed.

# Table is there after commit

select count(*) from ORA$PTT_MyData_session;

COUNT(*)

----------

# 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.

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

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!!!

Oracle 18c – Private Temporary Tables

Like this:

2 Comments

Leave a Reply Cancel reply

Share this:

Like this:

2 Comments

Leave a Reply Cancel reply