1. Overview
This document is about the new features in Oracle 18c is Private Temporary Tables.
2. Technologies and Tools Used
The following technologies have been used to achieve this
- Oracle SQL, PL/SQL
3. Use Case
- The table name must begin with the prefix defined in the PRIVATE_TEMP_TABLE_PREFIX initialization parameter. The default is “ORA$PTT_”.
- Permanent objects can’t reference private temporary tables directly.
- Indexes and views are not allowed on private temporary tables.
- Primary keys, or any constraint that requires an index, are not allows on private temporary tables.
- Columns can’t have default values.
- Private temporary tables can’t be accessed via database links.
4. Steps with Screenshot
Private temporary tables (Available from Oracle 18c ) are dropped at the end of the session/transaction depending on the definition of PTT.
- The ON COMMIT DROP DEFINITIONoption creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definitions and data.
- The ON COMMIT PRESERVE DEFINITIONoption creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.
You do not need to drop it manually. Oracle will do it for you.
SYNTAX:-
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table (
……
)
ON COMMIT DROP DEFINITION;
— or
ON COMMIT PRESERVE DEFINITION;
Example of ON COMMIT DROP DEFINITION (table is dropped after COMMIT is executed).
Example of ON COMMIT PRESERVE DEFINITION (table is retained after COMMIT is executed but it will be dropped at the end of the session).
Recent Posts
