Global Temporary Table

Global Temporary Table (GTT):
Data base version: Oracle Database 11g
Environment: DWH
This kind of table defines a temporary table for the current connection, these tables do not reside in the
system catalogues and are not persistent. Temporary tables exist only during the connection that declared
them and cannot be referenced outside of that connection. When the connection closes, the table rows are
deleted, and the table is dropped.
Temporary tables are useful when:

  • The table structure is not known before using an application
  • Data in the temporary table is needed while using the application
  • The table can be declared and dropped without holding the locks on the system catalogue

How to create an global temporary table:
CREATE GLOBAL TEMPORARY TABLE table name
( column name and its data type )
[ ON COMMIT {DELETE | PRESERVE} ROWS ]  ;
Option type:
ON COMMIT
Specifies the action taken on the global temporary table when a COMMIT operation is performed.
DELETE ROWS
This is the default value for ON COMMIT.
With option ON ROLLBACK DELETE ROWS this action will delete all the rows in the table only if the
temporary table was used.
With option ON COMMIT DELETE ROWS this action will delete the rows in the table even if the table was
not used.
PRESERVE ROWS
The rows of the table will be preserved.
ie:
DROP TABLE gtt3 PURGE; — purge option remove the table from the recycle bin
CREATE GLOBAL TEMPORARY TABLE gtt3(
key_leaf VARCHAR2(7),
field_name     VARCHAR2(50),
field_value  VARCHAR2(50) )
ON COMMIT PRESERVE ROWS
tablespace TEMP (*) ;
(*) tablespace cannot be specified for GTT's, because this kind of table are instantiated in the temporary
tablespace of the schema that inserts the data – not into "the default" temporary tablespace.
Around the scenery GTT’s:

  • Truncate statement only the session specific data is truncated
  • Indexes can be created on temporary tables
  • Views can be created against temporary tables
  • Temporary tables can have triggers

Excursus:

  • Global Temporary Tables and Undo
  • Global Temporary Tables and Redo
    Case of:
    • Export and Import 
    • Statistics on temporary tables 
    • Any restriction

    You may also like...

    Leave a Reply

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