Global Temporary Tables

Oracle introduced Global Temporary Tables (GTT) starting in Oracle8i for removing complex subqueries and allowing us to materialize the intermediate data that we need to solve a complex problem with SQL. Global temporary tables are an alternative to using the WITH clause to materialize intermediate query results.

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Global Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

ON COMMIT Setting	Implications
DELETE ROWS	This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS	This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.

This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables. For example, if you need to perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.

The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.

CREATE TEMPORARY TABLESPACE tbs_t1 

TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
    MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS
      TABLESPACE tbs_t1;

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. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  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 my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data. 

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. 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.

Mutating Error solution Using Global Temporary Table

An strategy is to use a Global Temporary Table (GTT) to handle . This is a better solution as the contents of the global temporary table are transactions, and so work as expected for automatic reruns of DML. First we need to create the temporary table to hold the data.

CREATE GLOBAL TEMPORARY TABLE tab1_mods (
  id      NUMBER(10),
  action  VARCHAR2(10)
) ON COMMIT DELETE ROWS;

Next, we recreate the package body to use the global temporary table in place of the collection.

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
BEGIN
  INSERT INTO tab1_mods (id, action) VALUES (p_id, p_action);
END tab1_row_change;

PROCEDURE tab1_statement_change IS
  l_count  NUMBER(10);
BEGIN
  FOR i IN (SELECT * FROM tab1_mods) LOOP
    SELECT COUNT(*)
    INTO   l_count
    FROM   tab1;

    INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
    VALUES (tab1_audit_seq.NEXTVAL, i.action, i.id, l_count, SYSTIMESTAMP);
  END LOOP;
  DELETE FROM tab1_mods;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS

Once again, the inserts and updates work as expected.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'THREE');

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'FOUR');

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

        ID DESCRIPTION
---------- -----------
         2 ONE
         3 TWO
         4 THREE
         5 FOUR

4 rows selected.

SQL> COLUMN created_time FORMAT A30
SQL> SELECT * FROM tab1_audit;

        ID ACTION        TAB1_ID RECORD_COUNT CREATED_TIME
---------- ---------- ---------- ------------ ------------------------------
         1 INSERT              2            1 23-NOV-2011 13:24:48.300250
         2 INSERT              3            2 23-NOV-2011 13:24:54.744229
         3 UPDATE              2            2 23-NOV-2011 13:25:01.170393
         4 UPDATE              3            2 23-NOV-2011 13:25:01.170837
         5 INSERT              4            3 23-NOV-2011 13:27:03.765868
         6 INSERT              5            4 23-NOV-2011 13:27:10.651991
         7 UPDATE              2            4 23-NOV-2011 13:27:18.097429
         8 UPDATE              3            4 23-NOV-2011 13:27:18.097957
         9 UPDATE              4            4 23-NOV-2011 13:27:18.098176
        10 UPDATE              5            4 23-NOV-2011 13:27:18.098801

10 rows selected.

SQL>

Source: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN11634