BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 18c Private Temporary Table Tips

2018-11-22

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

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.

Test

Creating a Private Temporary Table

Type:

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

View: DBA_PRIVATE_TEMP_TABLES,USER_PRIVATE_TEMP_TABLES

Sample:

Oracle 18c- Private Temporary Tables

Have a good work&life! 2018/11 via LinHong


Similar Posts

Comments