Oracle global temporary tables

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

Global temporary tables in Oracle are permanent database objects that store data on disk and 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]; 

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;

 Next, insert a new row into the temp1 table:

 INSERT INTO temp1(id,description)

VALUES(1,'Transaction specific global temp table');

Then, query data from the temp1 table: 

SELECT id, description FROM temp1; 

After that, commit the transaction:

COMMIT;

Finally, view the contents of the temp1 table again: 

SELECT id, description

FROM temp1; 

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

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; 

Second, insert a new row into the temp2 table:

 

 INSERT INTO temp2(id,description) VALUES(1,'Session specific global temp table'); 

Third, commit the transaction:

COMMIT; 

Fourth, view the contents of the temp2: 

SELECT id, description FROM temp2;

Fifth, commit the transaction:

COMMIT; 

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; 

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;

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; 

Next, insert a new row into the temp3 table:

INSERT INTO temp3(id) VALUES(1);

 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);

 Oracle issued the following error:

ORA-14450: attempt to access a transactional temp table already in use

 After that, commit the transaction in the first session:

COMMIT;

Finally, perform the DDL operation in the second transaction:

ALTER TABLE temp3

ADD description VARCHAR2(100);

 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.