Global Temporary Tables in Oracle

Global temporary table is also a similar table like normal Oracle table but the data in this table is session restricted. Such that data inserted by a session can only be accessed by that session, the data is flushed at the end of the session.

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

Purpose of Global Temporary Tables (GTT)

1. GTTs are used in situations where data fetch and passage is not possible in single stretch.
2. GTTs are used in situations where we want to store data temporarily in a table for calculating/pulling out some resultant data.

Syntax

CREATE GLOBAL TEMPORARY TABLE  
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS

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 COMMIT PRESERVE ROWS

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.

Some Important points related to Global Temporary Tables

1. Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
2. 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. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.
3. DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
4. If you rollback a transaction, the data you entered is lost, although the table definition persists.
5. 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.
6. 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.
7. It is good to know about that temporary table itself is not temporary, the data within it is temporary.
8. If your GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.
9. If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Restrictions on Global temporary tables

1. GTT can not be partitioned.
2. We can not create any Foreign key constraint on GTT
3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored
4. GTT cannot contain columns of nested table.
5. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Example of ‘ON COMMIT DELETE ROWS’ clause

Create a simple GTT as shown below:

CREATE GLOBAL TEMPORARY TABLE
temp_gtt (id NUMBER(20)) 
ON COMMIT DELETE ROWS;

Insert some data and do a SELECT

BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO temp_gtt VALUES (i) ;
  END LOOP ;
END ;
 /

Query for the data in the GTT

SELECT COUNT(*) FROM temp_gtt;

The result is

Do a commit and run the query again

COMMIT;
--
SELECT COUNT(*) FROM temp_gtt;

and the result is:

Example of ‘ON COMMIT PRESERVE ROWS’ clause

Create a simple GTT as shown below:

CREATE GLOBAL TEMPORARY TABLE
temp_gtt2 (id NUMBER(20)) 
ON COMMIT PRESERVE ROWS;

Insert some data and do a SELECT

BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO temp_gtt2 VALUES (i) ;
  END LOOP ;
END ;
 /

Query for the data in the GTT2

SELECT COUNT(*) FROM temp_gtt2;

The result is

Do a commit and run the query again

COMMIT;
--
SELECT COUNT(*) FROM temp_gtt2;

and the result is:

We can see that the records still exists as the table is created with ‘ON COMMIT PRESERVE ROWS’ clause, the rows would get deleted only at the end of the session.

To end the session, disconnect the database connection and reconnect again.

After reconnecting to the database, execute the below query and you could see that all the records got deleted.

SELECT COUNT(*) FROM temp_gtt2;

Please do comment below if you want any additional information or if you want to share something about GTT.