Global Temporary Tables
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. In Oracle 8i,
the maintenance and management of 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. 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;
Miscellaneous
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.
