Oracle Global Temporary Tables

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables.

Temporary Tables

Oracle support two types of temporary tables.

1. Global Temporary Tables : Available since Oracle 8i.
2. Private Temporary Tables : Available since Oracle 18c. details here.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.

CREATE GLOBAL TEMPORARY TABLE gtt_temp_table (
  id          NUMBER,
  description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO gtt_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         0

SQL>

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

CREATE GLOBAL TEMPORARY TABLE gtt_temp_table (
  id          NUMBER,
  description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO gtt_temp_table VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN tiger/scott

SELECT COUNT(*) FROM gtt_temp_table;

  COUNT(*)
----------
         0

SQL>

Note − Oracle does not allow you to specify any foreign key constraints on temporary tables. Attempting to create a foreign key on a temporary table will produce an ORA-14455 error.

Oracle Error: ORA-14455

Error Description:
Attempt to create referential integrity constraint on temporary table

Error Cause:
An attempt was made to create a referential integrity constraint on a temporary table. This is not supported.

For example, attempting to create a foreign key on a temporary table will produce ORA-14455, since Oracle does not support foreign keys in temporary tables.

Action:
Use triggers and/or do not attempt to create foreign keys on a temporary table.

Restrictions

• Temporary tables cannot be partitioned, clustered, or index organized.

• You cannot specify any foreign key constraints on temporary tables.

• Temporary tables cannot contain columns of nested table.

• You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.

• Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.

• The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace.

• Distributed transactions are not supported for temporary tables.

• A temporary table cannot contain INVISIBLE columns.