Global Temporary Tables

Global Temporary Table

It explains, for instance, why a Global Temporary Table is emptied when a table - even though it is another - is truncated within the same session.

Solution

GTT (Global Temporary Table), COMMIT and DDL

The Global Temporary Tables can be created in two different flavors:

    On commit, delete rows:

    When a Global Temporary Table is created using the following syntax...

    CREATE GLOBAL TEMPORARY TABLE <table_name> (
      <column_name> <column_data_type>,
      <column_name> <column_data_type>,
      <column_name> <column_data_type>)
    ON COMMIT DELETE ROWS;

    ... the Global Temporary Table <table_name> will be emptied and all rows will be deleted as soon as a COMMIT is issued. The idea of such a table is to keep the content available only for a short period of time, only when it is needed, the table is used, data stored and modified, but the data is removed if not necessary.

    Important:

        That effect on the Global Temporary Table will also happen if the COMMIT does not immediately follow operations on that Global Temporary Table : it will be emptied even if that happens a lot later, or after operations on other tables.

        The Global Temporary Table is not only available to the current session, but can be used by other sessions. However the data and operations on that table is private to the session. As the data is removed following a COMMIT, only the current session rows are emptied.
    On commit, preserve rows:

    When a Global Temporary Table is created using the following syntax...

    CREATE GLOBAL TEMPORARY TABLE <table_name> (
      <column_name> <column_data_type>,
      <column_name> <column_data_type>,
      <column_name> <column_data_type>)
    ON COMMIT PRESERVE ROWS;

    ... the Global Temporary Table is <table_name> will NOT be emptied and rows will be preserved as soon as a COMMIT is issued.

    Important:

        That is the closest to the usual behavior after a COMMIT: the modifications are applied on the table and the rows are preserved.

        The Global Temporary Table is not only available to the current session, but can be used by other sessions. However the data and operations on that table is private to the session.


On top of that, Oracle automatically issues COMMITS before and after every DDL (Data Definition Language) command (CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME).

As a consequence :

The Global Temporary Tables will be emptied when a DDL like TRUNCATE or CREATE is used if it was created with the ON COMMIT DELETE ROWS. The data it contained is removed, even if the TRUNCATE or CREATE concerns a totally different table.


Test case

    Create 3 test Global Temporary Tables table_a, table_b and table_c and insert some data

    SQL> CREATE GLOBAL TEMPORARY TABLE table_a
           (
           COLUMN001 NUMBER(9),
           COLUMN002 VARCHAR(30),
           COLUMN003 DATE
            ) ON COMMIT PRESERVE ROWS;

         CREATE GLOBAL TEMPORARY TABLE table_b
           (
           COLUMN001 NUMBER(9),
           COLUMN002 VARCHAR(30),
           COLUMN003 DATE
           ) ON COMMIT DELETE ROWS;

         CREATE GLOBAL TEMPORARY TABLE table_c
           (
           COLUMN001 NUMBER(9),
           COLUMN002 VARCHAR(30),
           COLUMN003 DATE
           ) ON COMMIT DELETE ROWS;

         INSERT INTO table_a VALUES(1,'VALUE_A1',SYSDATE);
         INSERT INTO table_a VALUES(2,'VALUE_A2',SYSDATE);
         INSERT INTO table_a VALUES(3,'VALUE_A3',SYSDATE);

         INSERT INTO table_b VALUES(1,'VALUE_B1',SYSDATE);
         INSERT INTO table_b VALUES(2,'VALUE_B2',SYSDATE);
         INSERT INTO table_b VALUES(3,'VALUE_B3',SYSDATE);

         INSERT INTO table_c VALUES(1,'VALUE_C1',SYSDATE);
         INSERT INTO table_c VALUES(2,'VALUE_C2',SYSDATE);
         INSERT INTO table_c VALUES(3,'VALUE_C3',SYSDATE);

    SQL> SELECT * FROM table_a;

    COLUMN001 COLUMN002 COLUMN003
    --------- --------- --------------
            1 VALUE_A1  20110621085738
            2 VALUE_A2  20110621085738
            3 VALUE_A3  20110621085738

    SQL> SELECT * FROM tref_b;

    COLUMN001 COLUMN002 COLUMN003
    --------- --------- --------------
            1 VALUE_B1  20110621085738
            2 VALUE_B2  20110621085738
            3 VALUE_B3  20110621085738

    SQL> SELECT * FROM tref_c;

    COLUMN001 COLUMN002 COLUMN003
    --------- --------- --------------
            1 VALUE_C1  20110621085739
            2 VALUE_C2  20110621085739
            3 VALUE_C3  20110621085740
    TRUNCATE table_a

    SQL> TRUNCATE TABLE table_a;

    SQL> SELECT * FROM table_a;

    no rows selected

    SQL> SELECT * FROM table_b;

    no rows selected

    SQL> SELECT * FROM table_c;

    no rows selected

        table_a : is empty as it has been truncated by the TRUNCATE command.

        table_b : is empty because it was defined with ON COMMIT DELETE ROWS

        table_c : is empty because it was defined with ON COMMIT DELETE ROWS
    If instead, the table_b was TRUNCATED (after running the insert statements again)

    SQL> TRUNCATE TABLE table_b;

    SQL> SELECT * FROM table_a;

    COLUMN001 COLUMN002 COLUMN003
    --------- --------- --------------
            1 VALUE_A1  20110621085738
            2 VALUE_A2  20110621085738
            3 VALUE_A3  20110621085738

    SQL> SELECT * FROM table_b;

    no rows selected

    SQL> SELECT * FROM table_c;

    no rows selected

        table_a : is NOT empty as it was defined with ON COMMIT PRESERVE ROWS

        table_b : is empty because it has been truncated by the TRUNCATE command.

        table_c : is empty because it was defined with ON COMMIT DELETE ROWS