How to Use Private Temporary Tables in Oracle

Intermediate results, such as a set of quotes being prepared or rows staged for a calculation, often need a table for just a moment. A global temporary table has a permanent definition shared by everyone. A private temporary table is private in definition and data: it exists only in the session that creates it, disappears by itself, and its creation does not commit the current transaction.

Code for This Guide

The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create private temporary table ora$ptt_name (column type [, ...])
  [on commit {drop definition | preserve definition}]

The name must begin with the prefix set by the PRIVATE_TEMP_TABLE_PREFIX parameter, ORA$PTT_ by default.

A Table for the Session

Example:

create private temporary table ora$ptt_quote (route_id number, fare number)
  on commit preserve definition;

insert into ora$ptt_quote values (1, 520);
select * from ora$ptt_quote;

select table_name, duration from user_private_temp_tables;

Output:

Private TEMPORARY created.

1 row inserted.

   ROUTE_ID    FARE
___________ _______
          1     520

TABLE_NAME       DURATION
________________ ___________
ORA$PTT_QUOTE    SESSION

USER_PRIVATE_TEMP_TABLES lists the session's private temporary tables and how long they last.

A Table for the Transaction

With the default, the table is gone after COMMIT.

Example:

-- the default, ON COMMIT DROP DEFINITION: the table disappears at the end of the transaction
create private temporary table ora$ptt_tx (n number);
insert into ora$ptt_tx values (1);
select count(*) as rows_now from ora$ptt_tx;
commit;
select count(*) as rows_after_commit from ora$ptt_tx;

Output:

Private TEMPORARY created.

1 row inserted.

   ROWS_NOW
___________
          1

Commit complete.

Error starting at line : 6
In command -
select count(*) as rows_after_commit from ora$ptt_tx
Error at Command Line : 6 Column : 43
Error report -
SQL Error: ORA-00942: table or view "NIMBUS"."ORA$PTT_TX" does not exist

The Name Must Have the Prefix

Example:

create private temporary table quote_scratch (n number);

Output:

Error starting at line : 1
In command -
create private temporary table quote_scratch (n number)
Error report -
ORA-00903: invalid table name

Private Compared with Global Temporary Tables

Related Guides

• How to Use the WITH Clause in Oracle SQL

Conclusion

Private temporary tables are session-private in both definition and data, need the ORA$PTT_ prefix, do not commit the transaction when created, and drop themselves at the end of the transaction or the session. Use them for short-lived intermediate data in ad hoc work and scripts.