Global Temporary Tables in Oracle

1. Overview

Many large applications make extensive use of Temporary data storage. Oracle implements the feature in Temporary tables. The article explains the features, restrictions, usage of Global Temporary tables.

2. Introduction

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

Note that only table data is session specific, but physically table is available in all sessions.

3. Syntax

Code :
CREATE GLOBAL TEMPORARY TABLE <Table-name>
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;
Explanation

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

4. Illustration

A global temporary table GTT_TEST is created with ON COMMIT DELETE ROWS status. This implies that GTT_TEST would get auto-truncate after each transaction.

Code sql:
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
 (
CUST_ID NUMBER,
COL1 VARCHAR2(100)
 ) ON COMMIT DELETE ROWS;

TABLE created.

SQL> INSERT INTO GTT_TEST
VALUES(1,'CLUB-ORACLE');

1 ROW created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM GTT_TEST;

no ROWS selected;
5. Notes


1. Often referred as GTT, they were introduced in Oracle 8i
2. Scope of TRUNCATE command is a single database session. There is no effect of TRUNCATE on other sessions.
3. Like physical heap tables, Indexes, views and triggers can be created using Temporary tables. But scope of all such objects is a session or a transaction
4. Temporary tables cannot be partitioned
5. Foreign key constraints are not applicable in case of Temporary tables
6. Temporary tables cannot contain column of persistent collection type
7. Use of LOB_STORAGE_CLAUSE, LOGGING/NOLOGGING, MONITORING/NOMONITORING, LOB_INDEX_CLAUSE is restricted in GTT definition. Prior to Oracle 11g, TABLESPACE cannot be defined for GTT i.e. GTT segments were created in user’s default tablespace. But after Oracle 11g, GTT segments can be created on other tablespaces too, provided the tablespace must be a Temporary tablespace.
For example,

Before Oracle 11g,

Code sql:
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST (
OBJECT_NAME VARCHAR2(10)
) ON COMMIT DELETE ROWS
TABLESPACE TEMP;
CREATE global TEMPORARY TABLE GTT_TEST
*
ERROR at line 1:
ORA-14451: unsupported feature WITH TEMPORARY TABLE
After oracle 11g

Code sql:
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
 (
 CUST_ID NUMBER,
COL1 VARCHAR2(100)
 ) ON COMMIT DELETE ROWS  TABLESPACE DEV;
CREATE global TEMPORARY TABLE GTT_TEST
*
ERROR at line 1:
ORA-02195: Attempt TO CREATE TEMPORARY object IN a NON-TEMPORARY tablespace

SQL> CREATE GLOBAL TEMPORARY TABLE GTT_TEST
 (
 CUST_ID NUMBER,
COL1 VARCHAR2(100)
 ) ON COMMIT DELETE ROWS  TABLESPACE TEMP;

TABLE created.

SQL> SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME='GTT_TEST';

TABLE_NAME     TABLESPACE_NAME
---------     ------------------------------
GTT_TEST        TEMP
8. Only GTT Table definitions, not the data can be exported/imported using Oracle EXP/IMP utilities.


Ur's
AmarAlam