Example of using Private Temporary Tables in Oracle 18c

Oracle already has a Global temporary table, but a Private temporary table is placed in the memory area but a Global temporary table is in Disk. You can create a private temporary table with a default prefix “ORA$PTT_” which is defined in the parameter private_temp_table_prefix. Global temp table available in all sessions but Private temp table only to the created session

SQL> show parameter private
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
private_temp_table_prefix            string      ORA$PTT_

Create the private temporary table

Syntax:

CREATE PRIVATE TEMPORARY TABLE table_name(
    column_definition,
    ...
) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION];

Examples:
--For Transaction:
create private temporary table ora$ptt_test ( id number) on commit drop definition;
insert into ora$ptt_test values(1);
commit;
-- on commit the table drop its definition:
desc ora$ptt_test; 
ORA-04043: object ora$ptt_test does not exists

--For Session:
create private temporary table ora$ptt_test ( id number) on commit preserve definition;
insert into ora$ptt_test values(1);
commit;
desc ora$ptt_test;
Name                Null?          Type
-----------------   ------------   --------------
id                                 Number

Note: Benefit for the transaction: we can now create different temporary tables for different transactions in a single session

Check information about private temporary table:

-- Check for complete database:
SELECT * FROM DBA_PRIVATE_TEMP_TABLES
-- Check for current Session 
SELECT * FROM USER_PRIVATE_TEMP_TABLES

Use in PL/SQL procedure or packages: It is temporary session objects so if we want to use them in PL/SQL code then we need to use them in dynamic SQL Query.

SQL> SET SERVEROUTPUT ON
SQL> Declare
    r_id number;
    p_id number := 1;
    BEGIN
    EXECUTE IMMEDIATE 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_test(id number) ON COMMIT DROP DEFINITION';
    EXECUTE IMMEDIATE 'INSERT INTO ora$ptt_test VALUES (1)';
    EXECUTE IMMEDIATE 'SELECT id INTO :r_id FROM ora$ptt_test WHERE id = :id' INTO r_id USING p_id;
    dbms_output.put_line(r_id);
   END;
   /
1

PL/SQL procedure successfully completed.

Error: While creating this private table we get the error:

SQL> create private temporary table ora$ptt_test ( id number) on commit drop definition
  2  *
  3  ERROR at line 1:
  4  ORA-14451: unsupported feature with temporary table

Cause: Run from SYS user

Solution; Create from any non sys user will fix the issue.