An Essential Guide to Oracle Global Temporary Tables By Examples

Summary: in this tutorial, you will learn about Oracle global temporary table and how to use the CREATE GLOBAL TEMPORARY TABLE to create a new transaction or session-specific global temporary table.

Introduction to Oracle global temporary tables

A temporary table is a table that holds data only for the duration of a session or transaction.

Oracle introduced the global temporary table concept in 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 are visible to all sessions.

However, 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.

CREATE GLOBAL TEMPORARY TABLE statement

To 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];Code language: SQL (Structured Query Language) (sql)

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.

Creating a global temporary table examples

Let’s practice with some examples of creating a global temporary table.

1) Creating a transaction-specific global temporary table example

First, create a transaction-specific global temporary table using the ON COMMIT DELETE ROWS option:

CREATE GLOBAL TEMPORARY TABLE temp1(
    id INT,
    description VARCHAR2(100)
) ON COMMIT DELETE ROWS;Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the temp1 table:

INSERT INTO temp1(id,description)
VALUES(1,'Transaction specific global temp table');Code language: SQL (Structured Query Language) (sql)

Then, query data from the temp1 table:

SELECT id, description 
FROM temp1;Code language: SQL (Structured Query Language) (sql)

After that, commit the transaction:

COMMIT;
Code language: SQL (Structured Query Language) (sql)

Finally, view the contents of the temp1 table again:

SELECT id, description 
FROM temp1;Code language: SQL (Structured Query Language) (sql)

It returned no row because Oracle truncated all rows of the temp1 table after the transaction is committed.

2) Creating a session-specific global temporary table example

First, create a session-specific global temporary table:

CREATE GLOBAL TEMPORARY TABLE temp2(
    id INT,
    description VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the temp2 table:

INSERT INTO temp2(id,description)
VALUES(1,'Session specific global temp table');Code language: SQL (Structured Query Language) (sql)

Third, commit the transaction:

COMMIT;Code language: SQL (Structured Query Language) (sql)

Fourth, view the contents of the temp2:

SELECT id, description 
FROM temp2;Code language: SQL (Structured Query Language) (sql)

Fifth, commit the transaction:

COMMIT;Code language: SQL (Structured Query Language) (sql)

Finally, disconnect the current session, connect to the database using a separate session, and check the content of the temp2table:

SELECT id, description 
FROM temp2;Code language: SQL (Structured Query Language) (sql)

It returned no row because Oracle truncated all rows of the temp2 table after the session ended.

Oracle global temporary tables & indexes

Oracle allows you to create indexes on global temporary tables.

However, the data in the index has the same scope as the data stored in the global temporary table, which exists during a transaction or session.

Global temporary table tables & tablespaces

By default, Oracle stores the data of the global temporary table in the default temporary tablespace of the table’s owner.

But you can explicitly assign another tablespace to a global temporary table during table creation using the TABLESPACE clause as shown in the following statement:

CREATE GLOBAL TEMPORARY TABLE table_name (
    column_definition,
    ...,
    table_constraints
) ON COMMIT [DELETE ROWS | PRESERVE ROWS]
TABLESPACE tablespace_name;
Code language: SQL (Structured Query Language) (sql)

This allows you to better manage temporary tablespaces.

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.

First, create a temporary table named temp3:

CREATE GLOBAL TEMPORARY TABLE temp3(
    id INT
) ON COMMIT DELETE ROWS;
Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the temp3 table:

INSERT INTO temp3(id) VALUES(1);
Code language: SQL (Structured Query Language) (sql)

Then, log in to the database in a separate session e.g., using SQL*Plus and add a column to the temp3 table:

ALTER TABLE temp3 
ADD description VARCHAR2(100);
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-14450: attempt to access a transactional temp table already in use
Code language: SQL (Structured Query Language) (sql)

After that, commit the transaction in the first session:

COMMIT;
Code language: SQL (Structured Query Language) (sql)

Finally, perform the DDL operation in the second transaction:

ALTER TABLE temp3 
ADD description VARCHAR2(100);
Code language: SQL (Structured Query Language) (sql)

This time it worked because no session is currently bound to the temp3 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.

In this tutorial, you have learned about Oracle global temporary tables and how to use the CREATE GLOBAL TEMPORARY TABLE to create a new transaction or session-specific global temporary table.

Was this tutorial helpful?