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.
System Privileges
ALTER ANY TABLE DELETE ANY TABLE LOCK ANY TABLE
CREATE ANY TABLE DROP ANY TABLE SELECT ANY TABLE
COMMENT ANY TABLE FLASHBACK ANY TABLE UNDER ANY TABLE
CREATE TABLE INSERT ANY TABLE UPDATE ANY TABLE
 
Create Global Temporary Table
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;
CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code   VARCHAR2(5),
by_user    VARCHAR2(30),
entry_date DATE);

-- or explicitly specifying the ON COMMIT action

CREATE GLOBAL TEMPORARY TABLE gtt_zip2 (
zip_code     VARCHAR2(5),
by_user      VARCHAR2(30),
entry_date   DATE)
ON COMMIT DELETE ROWS;

set linesize 121

SELECT table_name, tablespace_name, temporary, duration
FROM user_tables;

INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

SELECT *
FROM gtt_zip1;

COMMIT;

SELECT *
FROM gtt_zip1;

-- statistics collection demo: obviously there is a problem
INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

exec dbms_stats.gather_table_stats(USER, 'GTT_ZIP1');

SELECT *
FROM gtt_zip1;
 
Create Session-Specific Temporary Table
Create Session-Specific 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>,
<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';

-- results
Description Value Redo Generated
Baseline 254269080 -
Regular Table Run 254605916 336836
On Commit Delete 254742528 136612
On Commit Preserve 254879140 136612
 
Create Private Temporary Table
PTT Note The Oracle docs are incomplete with respect to Private Temporary Tables. Keep in mind these considerations
  1. 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.
  2. 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 CREATE PRIVATE TEMPORARY TABLE [<schema_name>.]<table_name>(
<column_name> <column_data_type>,
<column_name> <column_data_type>,
...)
ON COMMIT <DROP | PRESERVE> DEFINITION;
sho parameter private

NAME                       TYPE    VALUE
-------------------------- ------- ---------
private_temp_table_prefix  string  ORA$PTT_

CREATE PRIVATE TEMPORARY TABLE ora$ptt_ocdr1(
rid   NUMBER(10),
rname VARCHAR2(15))
ON COMMIT DROP DEFINITION;

Table created.

desc ora$ptt_ocdr1
Name            Null?    Type
--------------- -------- --------------------
RID                      NUMBER(10)
RNAME                    VARCHAR2(15)

CREATE PRIVATE TEMPORARY TABLE ora$ptt_ocdr2
ON COMMIT PRESERVE DEFINITION AS
SELECT srvr_id, netaddress FROM uwclass.servers;

Table created.

desc ora$ptt_ocdr2

desc ora$ptt_ocdr1
Name            Null?    Type
--------------- -------- -------------
SRVR_ID                  NUMBER(10)
NETADDRESS               VARCHAR2(15)

SELECT tablespace_name
FROM user_tables
WHERE table_name LIKE 'ORA$PTT%';

no rows selected

SELECT segment_name
FROM user_segments
WHERE segment_name LIKE 'ORA$PTT%';

no rows selected

col duration format a20

SELECT owner, table_name
FROM dba_private_temp_tables;

OWNER    TABLE_NAME     DURATION
-------- -------------- ------------
UWCLASS  ORA$PTT_OCDR1  TRANSACTION
UWCLASS  ORA$PTT_OCDR2  SESSION
 
Commenting
Comment a table COMMENT ON TABLE <table_name> IS '<comment>';
COMMENT ON TABLE gtt_zip1 IS 'US Postal Service Zip Codes';

SELECT table_name, comments
FROM user_tab_comments
WHERE comments IS NOT NULL;
Comment a column COMMENT ON COLUMN <table_name.column_name> IS '<comment>';
COMMENT ON COLUMN gtt_zip1.zip_code IS '5 Digit Zip Code';

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments IS NOT NULL;
 
Alter Table Column Clauses
Add a new column ALTER TABLE <table_name> MODIFY (<field_name data_type>);
desc gtt_zip1

ALTER TABLE gtt_zip1 ADD (map_id NUMBER(10));

desc gtt_zip1
Add More Than One New Column ALTER TABLE <table_name> MODIFY (<field_name data_type>, <field_name data type>);
ALTER TABLE gtt_zip1 ADD (map_coor VARCHAR2(10), map_ver VARCHAR2(3));

desc gtt_zip1
Rename A Column ALTER TABLE <table_name> RENAME COLUMN <current_name> TO <new_name>;
ALTER TABLE gtt_zip1 RENAME COLUMN map_coor TO map_coord;

desc gtt_zip1
Drop A Column ALTER TABLE <table_name> DROP COLUMN <column_name>;
ALTER TABLE gtt_zip1 DROP COLUMN map_coord;

desc gtt_zip1
Alter Table Change Data Type ALTER TABLE <table_name> MODIFY (<column_name new_data_type);
desc gtt_zip1

ALTER TABLE gtt_zip1 MODIFY (zip_code VARCHAR2(6));

desc gtt_zip1
Alter Table Change Data Type Multiple Fields ALTER TABLE <table_name>
MODIFY (<column_name> <data_type>, <column_name> <data_type>, ...);
desc gtt_zip1

ALTER TABLE gtt_zip1
MODIFY (zip_code VARCHAR2(7), entry_Date TIMESTAMP WITH TIME ZONE);

desc gtt_zip1
 
Drop Table
Drop Table Into Recycle Bin DROP TABLE [<schema_name>.]<table_name>;
DROP TABLE gtt_zip1;
Drop Table Completely Bypassing the Recycle Bin DROP TABLE <table_name> PURGE;
DROP TABLE gtt_zip3 PURGE;
 
Foreign Key Constraints
Temporary Tables and Foreign Key CREATE TABLE ptemp(
pid      NUMBER(5),
zip_code VARCHAR2(5));

ALTER TABLE ptemp
ADD CONSTRAINT pk_ptemp_pid
PRIMARY KEY (pid);

CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code   VARCHAR2(5),
by_user     VARCHAR2(30),
entry_date  DATE);

ALTER TABLE gtt_zip1
ADD CONSTRAINT pk_gtt_zip1
PRIMARY KEY (zip_code);

ALTER TABLE ptemp
ADD CONSTRAINT fk_ptemp_gtt
FOREIGN KEY (zip_code)
REFERENCING gtt_zip1 (zip_code);

ALTER TABLE gtt_zip1
ADD CONSTRAINT fk_gtt_ptemp
FOREIGN KEY (zip_code)
REFERENCING ptemp (zip_code);
 
Indexes
Indexing Global Temporary Table Columns CREATE GLOBAL TEMPORARY TABLE gtt_zip4(
zip_code   VARCHAR2(5),
by_user     VARCHAR2(30),
entry_date  DATE);

desc gtt_zip4

CREATE INDEX ix_gtt_zip4_user
ON gtt_zip1(by_user);

SELECT table_name, index_name, tablespace_name
FROM user_indexes;

-- GTT indexes are stored in the TEMP tablespace
Indexing Private Temporary Table Columns Is Not Supported CREATE INDEX ix_ptt
ON ora$ptt_ocdr2(srvr_id);
on ora$ptt_ocdr2(srvr_id)
*
ERROR at line 2:
ORA-14451: unsupported feature with temporary table

Related Topics
Constraints
Data Types & Subtypes
DDL Event Triggers
DDL Statements
External Tables
Heap Tables
Indexes
IOT (Index Organized Tables)
Nested Tables
Object Tables
Partitioned Tables
Truncate
XMLType Tables
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx