Lesson 33 : Global Temporary Tables (GTT)

Global Temporary Tables (GTT)

Global temporary tables can be used to store data temporarily, privately, persistently for a session or transaction. The data flushes out at defined instant automatically. The data in temporary tables are session specific but the table is available to across all the sessions. The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

Syntax :

CREATE GLOBAL TEMPORARY TABLE <Table-name> (Column-1 datatype, col2, datatype ...) ON COMMIT [DELETE | PRESERVE] ROW

ON COMMIT DELETE ROWS option is to have the data for a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.

ON COMMIT PRESERVE ROWS option is to have data for a SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Default is (if neither of above options specified) - ON COMMIT DELETE ROWS

Here are the few points to remember with GTT

• Data in temporary tables is stored in temp tablespace.
• Indexes can be created on temporary tables.
• When we TRUNCATE a temporary table, only the session specific data is truncated and no impact on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally. 
• 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.

Example:

SQL> create global temporary table session_report(login_id varchar(10),start_time date,end_time date) ON COMMIT PRESERVE ROWS;

Table created.