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