Global Temp Tables

A temporary table is a table that holds data only for the duration of a session or transaction. Temporary tables generally contain all of the features that ordinary tables have like triggers, join cardinality, information about rows and block etc. the main difference is that the temporary tables can't have foreign keys related to other tables.

• Oracle introduced the global temporary table concept since version 8i.
• Unlike temporary tables from other database products such as MySQL and SQL Server, global temporary tables in Oracle are permanent database objects that store data on disk and visible to all sessions.
• The data stored in the global temporary table is private to the session. In other words, each session can only access its own data in the global temporary table.

Note that Oracle 18c introduced the private temporary table, which is a memory-based temporary table that is automatically dropped at the end of a session or transaction.

o create a global temporary table, you use the CREATE GLOBAL TEMPORARY TABLE statement as follows:

CREATE GLOBAL TEMPORARY TABLE table_name (
  column_definition,
  ...,
  table_constraints
) ON COMMIT [DELETE ROWS | PRESERVE ROWS];

The syntax of creating a global temporary table and a permanent table are the same except for the keyword GLOBAL TEMPORARY and the clause ON COMMIT [DELETE ROWS | PRESERVE ROWS].

• The ON COMMIT clause specifies whether data in the table is transaction-specific or session-specific:
• The ON COMMIT DELETE ROWS clause specifies that the global temporary table is transaction-specific. It means that Oracle truncates the table (remove all rows) after each commit.
• The ON COMMIT PRESERVE ROWS clause specifies that the global temporary table is session-specific, meaning that Oracle truncates the table when you terminate the session, not when you commit a transaction.
• Oracle uses the ON COMMIT DELETE ROWS option by default if you omit the ON COMMIT clause.

Things to consider before creating a global temporary table:

These are the most important points to consider before you create a global temporary table.

1) DDL operation on global temporary tables

It is not possible to perform a DDL operation (except TRUNCATE) on an existing global temporary table if one or more sessions are currently bound to that table.

2) Transactions on transaction-specific global temporary tables

Oracle only allows one transaction at a time on a transaction-specific temporary table.

If you have several autonomous transactions in a single transaction scope, you must commit the previous autonomous transaction before the next transaction can use the table.

3) Rollback on transaction-specific global temporary tables

Rolling back (ROLLBACK) on the global temporary table will cause all data entered lost.

4) Backup & recovery on global temporary tables

Due to the nature of temporary tables, backup and recovery are not available in case of a system failure.