1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Global Temporary Tables in Oracle

Discussion in 'SQL PL/SQL' started by SBH, Nov 22, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Many large applications make extensive use of Temporary data storage. Oracle implements the feature in Temporary tables. The article explains the features, restrictions, usage of Global Temporary tables.

    2. Introduction

    Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

    Note that only table data is session specific, but physically table is available in all sessions.

    3. Syntax

    Code (Text):
    CREATE GLOBAL TEMPORARY TABLE <Table-name>
    (
    [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.

    4. Illustration

    A global temporary table GTT_TEST is created with ON COMMIT DELETE ROWS status. This implies that GTT_TEST would get auto-truncate after each transaction.

    Code (SQL):
    SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
     (
    CUST_ID NUMBER,
    COL1 VARCHAR2(100)
     ) ON COMMIT DELETE ROWS;

    TABLE created.

    SQL> INSERT INTO GTT_TEST
    VALUES(1,'CLUB-ORACLE');

    1 ROW created.

    SQL> COMMIT;

    Commit complete.

    SQL> SELECT * FROM GTT_TEST;

    no ROWS selected;
    5. Notes


    1. Often referred as GTT, they were introduced in Oracle 8i
    2. Scope of TRUNCATE command is a single database session. There is no effect of TRUNCATE on other sessions.
    3. Like physical heap tables, Indexes, views and triggers can be created using Temporary tables. But scope of all such objects is a session or a transaction
    4. Temporary tables cannot be partitioned
    5. Foreign key constraints are not applicable in case of Temporary tables
    6. Temporary tables cannot contain column of persistent collection type
    7. 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.
    For example,

    Before Oracle 11g,

    Code (SQL):
    SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST (
    OBJECT_NAME VARCHAR2(10)
    ) ON COMMIT DELETE ROWS
    TABLESPACE TEMP;
    CREATE global TEMPORARY TABLE GTT_TEST
    *
    ERROR at line 1:
    ORA-14451: unsupported feature WITH TEMPORARY TABLE
     
    After oracle 11g

    Code (SQL):

    SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
     (
     CUST_ID NUMBER,
    COL1 VARCHAR2(100)
     ) ON COMMIT DELETE ROWS  TABLESPACE DEV;
    CREATE global TEMPORARY TABLE GTT_TEST
    *
    ERROR at line 1:
    ORA-02195: Attempt TO CREATE TEMPORARY object IN a NON-TEMPORARY tablespace

    SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
     (
     CUST_ID NUMBER,
    COL1 VARCHAR2(100)
     ) ON COMMIT DELETE ROWS  TABLESPACE TEMP;

    TABLE created.

    SQL> SELECT TABLE_NAME,TABLESPACE_NAME
    FROM DBA_TABLES
    WHERE TABLE_NAME='GTT_TEST';

    TABLE_NAME     TABLESPACE_NAME
    ---------     ------------------------------
    GTT_TEST        TEMP
    8. Only GTT Table definitions, not the data can be exported/imported using Oracle EXP/IMP utilities