Global Temporary Tables

1. Overview

This Document is about the data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

2. Technologies and Tools Used

The following technologies have been used to achieve this

 

• Oracle SQL,PL/SQL

3. Use Case

• Data in a global temporary table is private.
• Data inserted by a session can only be accessed by that session.
• Indexes and views are allowed on Global temporary tables.
• Temporary tables can have triggers associated with them.

Types of GTT :

1. Transaction Specific GTT.
2. Session Specific GTT.

 

4. Steps with Screenshot

First, create a transaction-specific global temporary table using theON COMMIT DELETE ROWS option:1) Creating a transaction-specific global temporary table example

 

CREATE GLOBAL TEMPORARY TABLE temp1(

id INT,

description VARCHAR2(100)

) ON COMMIT DELETE ROWS;

 

• Next, insert a new rowinto the temp1 table:

 

INSERT INTO temp1(id,description)

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

 

• Then, query datafrom 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;

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.