Oracle Global and Private Temporary Tables Version 21c
General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Data Dictionary Objects
ALL_ALL_TABLES
COL$
TAB$
ALL_COL_COMMENTS
DBA_ALL_TABLES
USER_ALL_TABLES
ALL_TABLES
DBA_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COLS
DBA_PRIVATE_TEMP_TABLES
USER_PRIVATE_TEMP_TABLES
ALL_TAB_COMMENTS
DBA_TABLES
USER_TABLES
ALL_TAB_PRIVS
DBA_TAB_COLS
USER_TAB_COLS
CDB_ALL_TABLES
DBA_TAB_COMMENTS
USER_TAB_COMMENTS
CDB_PRIVATE_TEMP_TABLES
DBA_TAB_PRIVS
USER_TAB_PRIVS
CDB_TABLES
Oracle Documentation Error
Oracle documentation refers to two different types of Global Temporary Tables, ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS: This is not the syntax not the type of table if you want to understand the data dictionary.
In dcore.sql where TAB$ is defined, look at the PROPERTY column and you will clearly see the two different table types referred to as 0x00400000 = global temporary table and 0x00800000 = session-specific temporary table.
The Library use the more technically correct names as appropriate.
Global temporary tables have three major benefits:
1. Non-interference between private sets of data.
2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing).
3. Decreased redo generation as, by definition, they are non-logging.
However:
Mixing temporary tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses (badly).
Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.
Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.
Note: All DDL includes two implicit commits so any rows in a GTT specified with ON COMMIT DELETE ROWS will empty the table.
Create Global Temporary Table
You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace.
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>);
or explicitly specifying the ON COMMIT action
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>,
<column_name> <column_data_type>,
<column_name> <column_data_type>)
ON COMMIT DELETE ROWS;
You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace.
CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>,
<column_name> <column_data_type>,
<column_name> <column_data_type>)
ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE gtt_zip3 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE)
ON COMMIT PRESERVE ROWS;
set linesize 121
SELECT table_name, tablespace_name, temporary, duration
FROM user_tables;
INSERT INTO gtt_zip3
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);
SELECT *
FROM gtt_zip3;
COMMIT;
SELECT *
FROM gtt_zip3;
-- log on as a different user
-- log back on as original user
SELECT *
FROM gtt_zip3;
Temporary Table Demo
Redo Generation by GTT Type
CREATE TABLE reg_tab (
testcol VARCHAR2(100));
CREATE GLOBAL TEMPORARY TABLE gtt_ocd (
testcol VARCHAR2(100))
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE gtt_ocp (
testcol VARCHAR2(100))
ON COMMIT PRESERVE ROWS;
col value format 999999999999
-- get baseline redo value
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';
-- load 1000 rows into a heap table
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO reg_tab
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/
-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';
-- load 1000 rows into a GTT with ON COMMIT DELETE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocd
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/
-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';
-- load 1000 rows into a GTT with ON COMMIT PRESERVE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocp
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/
-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';
The Oracle docs are incomplete with respect to Private Temporary Tables. Keep in mind these considerations
A PTT's name must be prefixed with the parameter string value for "private_temp_table_prefix". If you don't like the Oracle Corp default, and I don't (too many bytes) change it.
You cannot create a PTT as SYS and possibly with other privileged accounts. If you try to do so the error message you get will be misleading: Ignore it and move to a non-privileged schema.
Create Private Temporary Table That Empties On Commit