Global Temporary Tables

Applications regularly utilize some type of impermanent information store for processes that are too convoluted to finish in a solitary pass. Regularly, these transitory stores are characterized as data set tables or PL/SQL tables. From Oracle 8i ahead, the upkeep and the board of transitory tables can be assigned to the waiter by utilizing Global Temporary Tables.

Types of Temporary Tables

The Oracle Database supports two types of temporary tables listed below:

1. Global Temporary Tables
2. Private Temporary Tables

Today we are going to discuss Global temporary tables here.

Global Temporary Tables

The global temporary tables are available since 8i. In the Global temporary table, the data is private. Like you inserted a data and only you can access the data in the same session. That data can be saved for the whole session or current transaction.

There are two types of clauses that we are using in GTT, listed below:

1. ON COMMIT DELETE ROWS clause
2. ON COMMIT PRESERVE ROWS clause

1. ON COMMIT DELETE ROWS CLAUSE

The ON COMMIT DELETE ROWS clause beckoning the table data must be removed at the end of the transaction, or session. As you can see in the below example:

Step 1: Create Global Temporary Table

Here we created a GTT with the name temp_table with two columns.

SQL> CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, report  VARCHAR2(20))
          ON COMMIT DELETE ROWS;  

Table created.

Step 2: Insert Data in GTT and Check

Insert some records in the Global temporary table and retrieve the records as in the below examples:

SQL> INSERT INTO temp_table VALUES (1, 'ONE');

1 row created.

SQL> SELECT COUNT(*) FROM temp_table;

  COUNT(*)
----------
         1

Step 3: Check Data after commit in GTT

As you can see we try to retrieve the data from GTT after commit but we were unable to find any data because of the ON COMMIT DELETE ROWS clause delete all the data from the GTT automatically after commit.

SQL> commit;

Commit complete.

SQL> SELECT COUNT(*) FROM temp_table;

  COUNT(*)
----------
         0

2. ON COMMIT PRESERVE ROWS

Inverse, the ON COMMIT PRESERVE ROWS clause demonstrates that rows should exist continue the end of the transaction. Rows are removed automatically once the session end.

Step 1: Create GTT with ON COMMIT PRESERVE ROWS Clause

SQL> CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, report VARCHAR2(20))
          ON COMMIT PRESERVE ROWS;

Step 2: Insert Records

Insert some records and commit the transaction then check the data. After commit we able to retrive the records from the table.

SQL> INSERT INTO temp_table VALUES (1, 'ONE');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM temp_table;

  COUNT(*)
----------
         1

Check after disconnect the current session, connect in new session.

SQL> conn / as sysdba
Connected.

SQL> SELECT COUNT(*) FROM temp_table;

  COUNT(*)
----------
         0

Undo tablespace and Global Temporary Tables

The global temporary tables data stored in temporary tablespace and allied undo is still stored into the normal undo tablespace, which is itself protected by redo, so using a global temporary tables does not decrease undo and redo allied with protecting the undo tablespace.

The below code makes an ordinary table, populates it and actually takes a look at how much undo utilized by the transaction.

Let me drop old table and create new one.

SQL> drop table temp_table purge;

Table dropped.

Create simple table and check undo utilization.

SQL> CREATE TABLE temp_table (id NUMBER, report  VARCHAR2(20));

Insert hug records in above table.

SQL> INSERT INTO temp_table WITH data AS (SELECT 1 AS id FROM dual CONNECT BY level < 10000)
          SELECT rownum, TO_CHAR(rownum)
          FROM   data a, data b
          WHERE  rownum <= 1000000;

Check undo untilization by the current session.

SQL> SELECT t.used_ublk, t.used_urec FROM   v$transaction t,
 v$session s
          WHERE  s.saddr = t.ses_addr
          AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Output from above query:

We now I'm going to repeat the same test, but this time I'm going to use Global temporary table.

SQL> DROP TABLE temp_table PURGE;

Table dropped.


----- Create Global Ttemporary  Table.

SQL> CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, report VARCHAR2(20))
         ON COMMIT PRESERVE ROWS;

Table created.

----- Insert huge records in Global Temporary Table.

SQL> INSERT INTO temp_table WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

---- Check used Undo

SQL> SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');  

This time you can see the undo utilization value is same as in privieous one.

Redo and Global Temporary Tables

Create simple table and insert hugs records in the table and check how much amount of redo generated by the transaction.

SQL> DROP TABLE temp_table PURGE;


----- Create Table.

SQL> CREATE TABLE temp_table ( id NUMBER, report  VARCHAR2(20));

SQL> SET AUTOTRACE ON STATISTICS;


----- Insert huge records in  Table.

SQL> INSERT INTO temp_table
          WITH data AS (
         SELECT 1 AS id
 FROM   dual
  CONNECT BY level < 10000)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

In case of simple: redo status

Lets repeet the same steps with GTT.

SQL> DROP TABLE temp_table PURGE;

Table dropped.


----- Create Global Ttemporary  Table.

SQL> CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, description  VARCHAR2(20))
ON COMMIT PRESERVE ROWS; 

Table created.

SQL> SET AUTOTRACE ON STATISTICS;

----- Insert huge records in Global Temporary Table.

SQL> INSERT INTO temp_table
WITH data AS (SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000; 

1000000 rows created.

Redo Status:

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

You Must Read:

• Oracle Database 21c Installation On Oracle Linux 7 | Oracle 21c Download
• What are the control file and datafile in Oracle?
• Managing the Oracle Database Flash Recovery Area
• How to add SWAP Space in Linux
• How do I add a new hard drive to Linux?

article referenced from oracle-base