Global and local temporary table in Oracle

Global and local temporary table in Oracle

Temporary table is used to store data temporary for a session and utilized in single session or transaction as you needed and defined while creating Temporary table in Oracle.

Notes:
1. Oracle does not support Local Temporary table. It has only Global temporary table.
2. Data is remain private at session level.

Global Temporary Table has two options:
1. ON COMMIT DELETE ROWS
Clause make sure data deleted at the end of the transaction or the end of the session level.

2. ON COMMIT PRESERVE ROWS
Clause make sure data remain persistent at the end of the transaction. Only be removed at the end of the session or manually delete command.

Create temporary table with both clauses
Example: Use of Clauses in Temporary table.
ON COMMIT DELETE ROWS

-- Create temporary table
CREATE GLOBAL TEMPORARY TABLE TEST_TEMP (id NUMBER) ON COMMIT DELETE ROWS;

---Insert into test_temp table
SQL> insert into test_temp values (1);
1 row created.

SQL> select count(*) from test_temp;
COUNT(*)
----------
1

--On commit fire it will complete transaction and table data is deleted.
SQL> commit;
Commit complete.

SQL> select count(*) from test_temp;
COUNT(*)
----------
0

ON COMMIT PRESERVE ROWS

-- Create temporary table
CREATE GLOBAL TEMPORARY TABLE TEST_TEMP (id NUMBER) ON COMMIT PRESERVE ROWS;

---Insert into test_temp table
SQL> insert into test_temp values (1);
1 row created.

--On commit fire, on complete transaction, data remain perserved in this it only lost at end of session or manually delete.
SQL> commit;
Commit complete.

SQL> select count(*) from test_temp;
COUNT(*)
----------
1

Example: Data is private at Session level in Oracle

-- Session 1: Create temporary table
CREATE GLOBAL TEMPORARY TABLE TEST_TEMP (id NUMBER) ON COMMIT PRESERVE ROWS;

---Session 1: Insert into test_temp table
SQL> insert into test_temp values (1);
1 row created.

---Session 1: test select query
SQL> select count(*) from test_temp;
COUNT(*)
----------
1

--Session 2: Create new session and test same query:
SQL> select count(*) from test_temp;
COUNT(*)
----------
0

Drop temporary table in Oracle

Drop table test_temp;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.