Oracle Temporary Table

Often database developers require temporary storage location which is private to session. To accomplish this requirement, Oracle database allows us to create Temporary tables. Temporary tables are of two types

1- Transaction Specific Temporary Table

A transaction specific temporary table holds data until a commit is performed. A transaction begins when first SQL statement is executed and ends when a commit or rollback is performed. When a transaction ends either by commit or rollback, data in transaction specific temporary table is lost.

Use following command to create a transaction specific temporary table

CREATE GLOBAL TEMPORARY TABLE temp_table
(emp_id number,
emp_name varchar2(50))
ON COMMIT DELETE ROWS;

Note ON COMMIT clause in table creation. If ON COMMIT clause is omitted, by default oracle creates a transaction specific temporary table.

2- Session Specific Temporary Table

A session specific temporary table holds data until session lasts. When a commit is performed on session specific temporary table, data is preserved in table. But the data is not visible to others session. The data is lost when session ends.

Session specific temporary table can be created using “PRESERVE ROWS” in ON COMMIT clause.

CREATE GLOBAL TEMPORARY TABLE temp_table
(emp_id number,
emp_name varchar2(50))
ON COMMIT PRESERVE ROWS;

The definition of temporary table is visible to all the sessions. Unlike permanent table, segment for temporary table is allocated when first INSERT statement is executed on temporary table. Indexes can also be created on temporary tables. The scope and life time of these indexes is similar to temporary tables.

1 thought on “Oracle Temporary Table”

  1. I just discovered a global temporary table in our system today. Imagine my surprise when I found the data was private to the session. It was driving me wild until I figured out this was a GTT.

Leave a Reply

Your email address will not be published. Required fields are marked *