Temporary Table in Oracle

Contents: Temporary Tables Concepts and Practical to Create Temporary Table

Concept 

 ü  Temporary table is used to store data temporary in a table.

 ü  A temporary table can be transaction specific or session specific. In both cases, the data inserted by a session is private to the session.

 ü  The Create Global temporary table statement creates a temporary table.

 ü  You can create indexes, view and triggers on temporary tables. and you can also use Export and Import or Data Pump to export and import the definition of a temporary table.

 ü  Temporary table is always created in the users temporary tablespace. no other tablespace can be specified.

Transaction Specific

In Transaction specific temporary tables, data existed for the duration of the transactions.

On commit delete rows:- to specify that the lifetime of the inserted rows is the for the duration of the transaction only.

SQL> Create global temporary table on commit delete rows as select * from employees;

Session Specific

For session -specific temporary tables, data exists for the duration of the session.

On commit preserve rows:- to specify that the lifetime of the inserted rows is for the duration of the session .

SQL> create global temporary table on commit preserve rows as select * from employees;