Miscellaneous Features of Global Temporary Tables

• 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.

Restrictions on Declared Global Temporary Tables

You cannot use the following features with temporary tables:

• Synonyms, triggers and views on SESSION schema tables (including physical tables and temporary
         tables)
• Caching statements that reference SESSION schema tables and views
• Temporary tables cannot be specified in referential constraints and primary keys
• Temporary tables cannot be referenced in a triggered-SQL-statement
• Check constraints on columns
• Generated-column-spec
• Importing into temporary tables

If a statement that performs an insert, update, or delete to the temporary table encounters an error, all the rows of the temporary table are deleted.

The following data types cannot be used with Declared Global Temporary Tables:

• BLOB
• CHAR FOR BIT DATA
• CLOB
• LONG VARCHAR
• LONG VARCHAR FOR BIT DATA
• VARCHAR FOR BIT DATA
• XML

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 temp_tab (
  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_tab (
  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. Therefore, if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then 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.

Global Temporary Tables - Oracle

Applications often use some form of temporary data store for processes that are too complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
These tables do not reside in the system catalogs 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 rows of the table are deleted, and the in-memory description of the temporary table is dropped.

Temporary tables are useful when: 

• The table structure is not known before using an application.

• Other users do not need the same table structure.

• 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 catalog.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-Name

    { column-definition [ , column-definition ] * }

[ ON COMMIT {DELETE | PRESERVE} ROWS ]  

NOT LOGGED [ON ROLLBACK DELETE ROWS]

table-Name

Names the temporary table. If a schema-Name other than SESSION is specified, an error will occur (SQLSTATE 428EK). If the schema-Name is not specified, SESSION is assigned. Multiple connections can define declared global temporary tables with the same name because each connection has its own unique table descriptor for it.

Using SESSION as the schema name of a physical table will not cause an error, but is discouraged. The SESSION schema name should be reserved for the temporary table schema.

column-definition

See column-definition for CREATE TABLE for more information on column-definition. DECLARE GLOBAL TEMPORARY TABLE does not allow generated-column-spec in the column-definition.

Data type

Supported data types are: 

• BIGINT

• CHAR

• DATE

• DECIMAL

• DOUBLE

• DOUBLE PRECISION

• FLOAT

• INTEGER

• NUMERIC

• REAL

• SMALLINT

• TIME

• TIMESTAMP

• VARCHAR

ON COMMIT

Specifies the action taken on the global temporary table when a COMMIT operation is performed.

DELETE ROWS

All rows of the table will be deleted if no hold-able cursor is open on the table. This is the default value for ON COMMIT. If you specify ON ROLLBACK DELETE ROWS, this will delete all the rows in the table only if the temporary table was used. ON COMMIT DELETE ROWS will delete the rows in the table even if the table was not used (if the table does not have hold-able cursors open on it). 

PRESERVE ROWS

The rows of the table will be preserved.

NOT LOGGED

Specifies the action taken on the global temporary table when a rollback operation is performed. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed, if the table was created in the unit of work (or savepoint), the table will be dropped. If the table was dropped in the unit of work (or savepoint), the table will be restored with no rows.

ON ROLLBACK DELETE ROWS

This is the default value for NOT LOGGED. NOT LOGGED [ON ROLLBACK DELETE ROWS ]] specifies the action that is to be taken on the global temporary table when a ROLLBACK or (ROLLBACK TO SAVEPOINT) operation is performed. If the table data has been changed, all the rows will be deleted.