Understanding Global Temporary Table In Oracle

gtt

GLOBAL TEMPORARY TABLE

Applications often use some form of temporary data store for processes that are too complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables (Collections). From Oracle 8i onwards, Oracle gives us another option of temporary data storage in the form of Global Temporary Tables.

In Oracle a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specfic or session-specific basis.

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

• 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.

The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction using ON COMMIT clause.

The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

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

1 row created.

SQL> SELECT COUNT(*) FROM my_temp_table;

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

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM my_temp_table;

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

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

--Even after disconnecting the session and connect back again, the above GTT can be seen :-

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

The ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

--Session ::: 1
SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT PRESERVE ROWS;

Table created.

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

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

1 row created.

SQL> SELECT COUNT(*) FROM my_temp_table;

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

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM my_temp_table;

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

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

--Session ::: 2
SQL> SELECT COUNT(*) FROM my_temp_table;

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

SQL> SELECT table_name,tablespace_name FROM user_tables WHERE LOWER(table_name) = 'my_temp_table';

TABLE_NAME                 TABLESPACE_NAME
------------------ ---------------------------
MY_TEMP_TABLE

SQL> SELECT object_name,object_type FROM all_objects WHERE object_name = 'MY_TEMP_TABLE';

OBJECT_NAME              OBJECT_TYPE
------------------- -----------------
MY_TEMP_TABLE              TABLE

--Session ::: 1		 
SQL> DROP TABLE my_temp_table PURGE;
DROP TABLE my_temp_table PURGE
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

--This error occurs when it is tried to perform a DDL on a GLOBAL TEMPORARY TABLE with ON COMMIT PRESERVE ROWS when the session trying to do the DDL has already made a DML on the table.

--In order to do a DDL, the table must first be either truncated or the session must be exited.

--Session ::: 1
SQL> TRUNCATE TABLE my_temp_table;

Table truncated.

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

GLOBAL TEMPORARY TABLE WITH REDO & UNDO TABLESPACE

A Global Temporary Table stores its data in a Temporary Tablespace.Performing DML on a Global Temporary Table doesn’t generate redo, because the data is stored in a temporary tablespace, but it does generate undo ( in case of ROLLBACK) which in turn will generate redo to protect undo since the undo is stored in a undo tablespace.

Thus,operations such as INSERT, UPDATE, MERGE, and DELETE on global temporary tables generates direct undo but indirect redo.

From 12c onwards, undo for GTT can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will almost no longer generate redo.

This can be achieved using the parameter – TEMP_UNDO_ENABLED

SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE

SQL> CREATE TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  );

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       302       6237

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       303       6238
	   
SQL> TRUNCATE TABLE my_temp_table;

Table truncated.

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       302       6238

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

-- We can see, there is no significant difference in the undo used.
SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE

SQL> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

Session altered.

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     TRUE

SQL> CREATE TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  );

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       302       6237

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       302       6238

SQL> TRUNCATE TABLE my_temp_table;

Table truncated.

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

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

 USED_UBLK  USED_UREC
---------- ----------
       303       6238

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

-- We can see, there is no significant difference in the undo used.
SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE

SQL> CREATE TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  );

Table created.

SQL> SET AUTOTRACE ON STATISTICS;

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         96  recursive calls
      22699  db block gets
       1367  consistent gets
          0  physical reads
   23330912  redo size
        494  bytes sent via SQL*Net to client
       1072  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         40  recursive calls
      15320  db block gets
       2348  consistent gets
          0  physical reads
    2919000  redo size
        494  bytes sent via SQL*Net to client
       1076  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> TRUNCATE TABLE my_temp_table;

Table truncated.

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         39  recursive calls
      15312  db block gets
       2341  consistent gets
          0  physical reads
    2918708  redo size
        494  bytes sent via SQL*Net to client
       1076  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

-- We can see we have created an order of magnitude less redo when using the GTT, but we have not eliminated it.
SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE

SQL> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

Session altered.

SQL> SHOW PARAMETER TEMP_UNDO_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     TRUE

SQL> CREATE TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  );

Table created.

SQL> SET AUTOTRACE ON STATISTICS;

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         98  recursive calls
      22712  db block gets
       1420  consistent gets
          0  physical reads
   23331328  redo size
        494  bytes sent via SQL*Net to client
       1076  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> DROP TABLE my_temp_table PURGE;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT PRESERVE ROWS;

Table created.

SQL> SET AUTOTRACE ON STATISTICS;

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         41  recursive calls
      15335  db block gets
       2343  consistent gets
          0  physical reads
        664  redo size
        195  bytes sent via SQL*Net to client
        529  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          3  sorts (memory)
0	sorts (disk)
    1000000  rows processed

SQL> UPDATE my_temp_table SET description = description||'X' WHERE ROWNUM <= 2;

2 rows updated.

Statistics
----------------------------------------------------------
          6  recursive calls
          2  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        424  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> TRUNCATE TABLE my_temp_table;

Table truncated.

SQL> DROP TABLE my_temp_table;

Table dropped.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  2    id           NUMBER,
  3    description  VARCHAR2(20)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO my_temp_table
  2  WITH data AS (
  3    SELECT 1 AS id
  4    FROM   dual
  5    CONNECT BY level < 10000
  6  )
  7  SELECT rownum, TO_CHAR(rownum)
  8  FROM   data a, data b
  9  WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         37  recursive calls
      15321  db block gets
       2334  consistent gets
          0  physical reads
        664  redo size
        195  bytes sent via SQL*Net to client
        529  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> UPDATE my_temp_table SET description = description||'X' WHERE ROWNUM <= 2;

2 rows updated.

Statistics
----------------------------------------------------------
          6  recursive calls
          2  db block gets
         70  consistent gets
          0  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        424  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

--We can see we have created an order of magnitude less redo when using the GTT, but we have not eliminated it.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “PL/SQL Collection Methods”

  1. When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database uses a collection method such as An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically (however, a maximum limit is imposed see Referencing Collection Elements ).

Leave a Reply

Your email address will not be published. Required fields are marked *