Oracle 18C new feature: Private Temporary Tables

Share via:

Post Views: 0

Dear Readers,

In this article we will focus on new feature that has been introduced in Oracle 18C that is “Private Temporary Table”. Private temporary tables are temporary database objects that are dropped at the end of end of a session or transaction depending on the setup.

HOW IT DIFFERS FROM GLOBAL TEMPORARY TABLES

The below tables explains key differences between this two tables:

SYNTAX

To create a new private temporary table, you use the CREATE PRIVATE TEMPORARY TABLE statement:

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

CREATE PRIVATE TEMPORARY TABLE table_name(

column_definition,

...

) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION];

PRIVATE TEMPORARY TABLE TYPES

Private Temporary Tables are of two types:

Transaction Specific:

By using ON COMMIT DROP DEFINITION option a private temporary table will be transaction-specific. At the end of the transaction (after commit or rollback), Oracle drops both table definition and data. This is default type in case you do not specify ON COMMIT clause.

Session Specific:

By using ON COMMIT PRESERVE DEFINITION option a private temporary table will be session-specific. Oracle removes all data and drops the table at the end of the session.

NAMING CONVENTION

Names of private temporary tables must be prefixed according to the initialization parameter private_temp_table_prefix. Default value is ORA$PTT_.

You can alter value for “private_temp_table_prefix” using spfile option.

EXAMPLES

Transaction Specific

Session Specific

ADVANTAGES

By using this we can now create different temporary tables for different transactions in single session.
No additional maintenance is required for Private Temporary tables.
Now different sessions of the same user can share same name for a temporary table.
This can be very helpful in “Read Only” databases.

LIMITATIONS

Permanent objects can’t reference private temporary tables directly.
Indexes, materialized views, and zone maps can’t be created on private temporary tables.
Private Temporary Table columns can’t have default values.
These tables can’t be accessed via database links.

Hope u will find this post very useful. 🙂

Stay tuned for more articles on different topics.

Cheers!!!

Regards,

Adityanath

Email ID: adityanath.dewoolkar@gmail.com

LinkedIn: www.linkedin.com/in/adityanath-dewoolkar-07253123/

Share via:

Note: Please test scripts in Non Prod before trying in Production.

Adityanath Dewoolkar

Share this post

Tags