PL/SQL Global Temporary Tables (GTT)

Sometimes we need to store data temporarily in a table . From Oracle 8i onward, we can manage temporary tables can be delegated to the server by using Global Temporary Tables.

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.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

  Other Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

Reference : http://www.oracle-base.com/articles/misc/temporary-tables.php