Oracle Global Temporary Table

Temporary tables were introduced from Oracle 8i onwards and is called global temporary tables. This is called temporary table because it is created when the data is inserted and is similar to normal tables.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10));

The definition of this table is visible to all sessions, but the data is visible to only the session that creates this table. This is used by developers to store session/transaction specific data which can be ignored at the end of the session/transaction. On issuing a truncate on this table the data that is specific to the current session alone will get deleted.

It is possible to store the session specific data in this temporary table with the help of an additional cluase that we use while creating the table, on commit. We can define the temporary table either to delete or store the session specific data with "on commit" parameter.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10))
on commit delete rows;

Here as soon as the transaction ends with a commit, the records in the table are deleted.

Syntax:
create global temporary table temp_data
( emp_id number, emp_name varchar2(10))
on commit preserve rows;

The above definition will preserve the data in the temporary table even after end of a transaction.

Limitations:

Temporary tables cannot contain nested tables or varray types or they cannot be partitioned, index-organized or clustered. They cannot be used in parallel DML or parallel queries and distributed transactions are not supported on these tables.