Oracle Private Temporary Table

 

In Oracle Database, global temporary tables  are permanent objects whose data are stored on disk and automatically deleted at the end of a session or transaction. In addition, global temporary tables are visible to all sessions currently connected to the database.

Oracle 18c introduced private temporary tables whose both table definition and data are temporary and are dropped at the end of a transaction or session. On top of that, Oracle stores private temporary tables in memory and each temporary table is only visible to the session which created it.

Naming rules of the private temporary tables

All private temporary tables have a prefix defined by the PRIVATE_TEMP_TABLE_PREFIX initialization parameter, which defaults to to ORA$PTT_.

Create private temporary table statement

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]; 

In this syntax:

First, specify the name of the temporary table, which follows the naming rule mentioned above.

Second, specify a list of columns with their definitions.

Third, use the ON COMMIT clause to indicate whether the table is transaction-specific or session-specific:

·       The ON COMMIT DROP DEFINITION option creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definition and data.

·       The ON COMMIT PRESERVE DEFINITION option creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.

By default, Oracle uses ON COMMIT DROP DEFINITION if you omit the ON COMMIT option.

Private temporary tables vs. global temporary tables

This table illustrates the differences between global temporary tables and private temporary tables:

Characteristic	Global temporary tables	Private temporary tables
Naming rule	Same as for permanent tables	By default, must be prefixed with ORA$PTT_ .
Visibility	All sessions	Only the session that created the table.
Storages	Disk	Memory only
Table types	Transaction-specific (ON COMMIT DELETE ROWS) or session-specific (ON COMMIT PRESERVE ROWS)	Transaction-specific (ON COMMIT DROP DEFINITION) or session-specific (ON COMMIT PRESERVE DEFINITION)

Oracle private temporary table examples

Let’s take some examples of using the CREATE PRIVATE TEMPORARY TABLE statement.

1) Creating a private temporary table example which is transaction-specific

First, create a new temporary table that is transaction-specific:

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1(

    id INT,

    description VARCHAR2(100)

) ON COMMIT DROP DEFINITION;

 

Next, insert a row into the ora$ppt_temp1 table:

INSERT INTO ora$ppt_temp1(id,description)

VALUES(1,'Transaction-specific private temp table'); 

Then, view the contents of the ora$ppt_temp1 table: 

SELECT id, description FROM ora$ppt_temp1;

 After that, commit the transaction:

COMMIT;

 Finally, query data from the ora$ppt_temp1 table:

 

SELECT id, description FROM ora$ppt_temp1;

 

Oracle issued the following error because it already dropped the ora$ppt_temp1 table at the end of the transaction.

ORA-00942: table or view does not exist

2) Creating a private temporary table which is session-specific

First, create a new temporary table that is session specific:

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp2(

    id INT,

    description VARCHAR2(100)

) ON COMMIT PRESERVE DEFINITION; 

Next, insert a row into the ora$ppt_temp2 table: 

INSERT INTO ora$ppt_temp2(id,description)

VALUES(1,'Session-specific private temp table'); 

Then, view data of the ora$ppt_temp2 table: 

SELECT id, description

FROM ora$ppt_temp2;

After that, commit the transaction and check contents of the ora$ppt_temp2 table:

COMMIT;

 SELECT id, description FROM ora$ppt_temp2; 

Finally, reconnect and view the contents of the ora$ppt_temp2 table:

 SELECT id, description FROM ora$ppt_temp2;

 

Oracle issued the following error because it removed the ora$ppt_temp2 table at the end of the session.

ORA-00942: table or view does not exist

Private temporary table Limitations

Private temporary tables share the same limitations of global temporary tables and the following additional restrictions:

·       The table name must have a prefix defined in the PRIVATE_TEMP_TABLE_PREFIX initialization parameter which is default to ORA$PTT_.

·       Permanent database objects cannot directly reference private temporary tables.

·       Indexes and materialized views cannot be created on private temporary tables.

·       Columns of the private temporary table cannot have default values.

·       Private temporary tables cannot be accessed via database links.