Oracle 18c introduced, Private temporary tables concept which are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.

The “CREATE PRIVATE TEMPORARY TABLE” statement to create a private temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific.

Here is my explanation on Oracle Live SQL.

This statement creates a private temporary table that is transaction specific:

We can overwrite this default behaviour with below command and can preserve the definition.

Private temporary tables must be prefixed with “ORA$PTT_” and this can’t be used for regular table.

Private temporary tables are useful in the following situations:

  • 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 database.

Mandy!!!

Leave a Reply