Global Temporary Table

Global Temporary Table
  • 24
    Ott

Global Temporary Table

twittergoogle_pluslinkedin

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