Oracle introduced a new feature in 18c release called “Private Temporary Tables”, This feature will essentially enable you to create a temporary memory based table, and this table will ONLY be viewed by the database session created this table. Moreover, here I will compare it with another old Oracle feature “Global Temporary Tables”.

The parameter for the predefined naming is PRIVATE_TEMP_TABLE_PREFIX

SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX

To simulate an example of creating a temporary private table:

SQL> alter session set container=ORCLPDB1;

Create a local database user in the pluggable database:

SQL> CREATE USER HR IDENTIFIED BY hr_123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

SQL> grant create session to hr;

SQL> grant resource to hr;

SQL> alter user hr quota unlimited on USERS;

SQL> alter user hr default role all;

Connect to the pluggable database as hr user and execute the following to create a private temporary table:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_records

(time_id      DATE,

quantity_of_sale  NUMBER(10,2))

ON COMMIT PRESERVE DEFINITION;

I will execute the following queries to insert a record in the temporary table:

SQL> insert into ORA$PTT_sales_records values(sysdate,1000);

SQL> commit;

If I open another session with the same database account, I can’t query the newly created private temporary table as shown below:

the following are the database views:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

CDB_PRIVATE_TEMP_TABLES

Its worth mentioning that if you attempt to create a private temporary table using SYS account, the following error will be thrown:

ORA-14451: unsupported feature with temporary table