Oracle 23c New Features for DBA'sStarting 18c, we can create private temporary tables which can be visible to the session that created it using "Create Private temporary table" command.
Following are use cases for private temporary tables
- When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session
- When a session is maintained indefinitely and must create different temporary tables for different transactions
- When the creation of a temporary table must not start a new transaction or commit an existing transaction
- When different sessions of the same user must use the same name for a temporary table
- When a temporary table is required for a read-only databaseNote: Names of private temporary tables must be prefixed according to the initialization parameter
private_temp_table_prefix.
This statement creates a private temporary table that is transaction specific:
CREATE PRIVATE TEMPORARY TABLE GEEK$PTT_test_transaction (time_id DATE, amount_sold NUMBER(10,2)) ON COMMIT DROP DEFINITION;
This statement creates a private temporary table that is session specific:
CREATE PRIVATE TEMPORARY TABLE GEEK$PTT_test_session (time_id DATE, amount_sold NUMBER(10,2)) ON COMMIT PRESERVE DEFINITION;
-Thanks
Geek DBA
Follow Me!!!