Sunday, April 27, 2014

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

    No comments:

    Post a Comment