Oracle 18c Private Temporary Table Tips
Private Temporary Table
Private Temporary Table is Available from Oracle 18c. A temporary table holds data that exists only for the duration of a transaction or session. Also we cannot create a PTT as SYS and possibly with other privileged accounts.
Private temporary tables are useful for dynamic reporting applications. Memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.
The document is PRIVATE TEMPORARY TABLE
Restrictions on Temporary Tables
Temporary tables are subject to the following restrictions: Temporary tables cannot be partitioned, clustered, or index organized. You cannot specify any foreign key constraints on temporary tables. Temporary tables cannot contain columns of nested table. You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause. Parallel UPDATE, DELETE and MERGE are not supported for temporary tables. The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace. Distributed transactions are not supported for temporary tables. A temporary table cannot contain INVISIBLE columns.
Restrictions on Private Temporary Tables
In addition to the general limitations of temporary tables, private temporary tables are subject to the following restrictions: The name of private temporary tables must always be prefixed with whatever is defined with the init.ora parameter PRIVATE_TEMP_TABLE_PREFIX. The default is ORA$PTT_. You cannot create indexes, materialized views, or zone maps on private temporary tables. You cannot define column with default values. You cannot reference private temporary tables in any permanent object, e.g. views or triggers. Private temporary tables are not visible through database links.
Creating a Private Temporary Table
Default Type: ON COMMIT DROP DEFINITION (the table should be dropped at the end of the transaction, or the end of the session.) ON COMMIT PRESERVE DEFINITION (the table and any data should persist beyond the end of the transaction. The table will be dropped at the end of the session.) Naming Rules： ORA$PTT_ Visibility: Only in created table session DDL storage: Memory
Have a good work&life! 2018/11 via LinHong