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 that the data should be deleted at the end of the transaction.
Many large applications make extensive use of Temporary data storage. Oracle implements the feature in Temporary tables.

Syntax

Code :
CREATE GLOBAL TEMPORARY TABLE  
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

Explanation

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Features

Often referred as GTT, they were introduced in Oracle 8i.


  1. 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.
  2. Data in temporary tables is stored in temp segments in the temp tablespace.
  3. Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
  4. 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.
  5. Views can be created against temporary tables and combinations of temporary and permanent tables.
  6. Temporary tables can have triggers associated with them.
  7. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
  8. Statistics on temporary tables are common to all sessions.
  9. Foreign key constraints are not applicable in case of Temporary tables.
  10. Temporary tables cannot be partitioned.
  11. Use of LOB_STORAGE_CLAUSE, LOGGING/ NOLOGGING, MONITORING/ NOMONITORING, LOB_INDEX_CLAUSE is restricted in GTT definition. Prior to Oracle 11g, TABLESPACE cannot be defined for GTT i.e. GTT segments were created in user’s default tablespace. But after Oracle 11g, GTT segments can be created on other tablespaces too, provided the tablespace must be a Temporary tablespace.
  12. Temporary tables cannot contain column of persistent collection type.

Comments

Post a Comment