Oracle Private Temporary Table

Summary: in this tutorial, you will learn about Oracle private temporary table and how to use the CREATE PRIVATE TEMPORARY TABLE statement to create a new private temporary table.

Introduction to Oracle private temporary tables

If you are familiar with other database products such as SQL Server, PostgreSQL, and MySQL, you might be confused by the temporary table concept in Oracle Database.

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];
Code language: SQL (Structured Query Language) (sql)

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:

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 that 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;Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO ora$ppt_temp1(id,description)
VALUES(1,'Transaction-specific private temp table');Code language: SQL (Structured Query Language) (sql)

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

SELECT id, description
FROM ora$ppt_temp1;Code language: SQL (Structured Query Language) (sql)

After that, commit the transaction:

COMMIT;Code language: SQL (Structured Query Language) (sql)

Finally, query data from the ora$ppt_temp1 table:

SELECT id, description
FROM ora$ppt_temp1;Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO ora$ppt_temp2(id,description)
VALUES(1,'Session-specific private temp table');
Code language: SQL (Structured Query Language) (sql)

Then, view data from the ora$ppt_temp2 table:

SELECT id, description
FROM ora$ppt_temp2;
Code language: SQL (Structured Query Language) (sql)

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

COMMIT;

SELECT id, description
FROM ora$ppt_temp2;
Code language: SQL (Structured Query Language) (sql)

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

SELECT id, description
FROM ora$ppt_temp2;Code language: SQL (Structured Query Language) (sql)

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 existCode language: SQL (Structured Query Language) (sql)

Private temporary table Limitations

Private temporary tables share the same limitations as 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 defaulted 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.

In this tutorial, you have learned how to use the Oracle CREATE PRIVATE TEMPORARY TABLE statement to create a new private temporary table that is transaction or session-specific.