Oracle Global Temporary Tables

GLOBAL TEMPORARY

The data in a global temporary table is private,The data in a temporary table is visible only to the session that inserts the data into the table.

A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below)

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.

DELETE ROWS

Specify DELETE ROWS for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS

Specify PRESERVE ROWS for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.

Temporary Table Example

The following statement creates a temporary table flight_schedule for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.

 Ex:
   CREATE GLOBAL TEMPORARY TABLE flight_schedule (
   startdate DATE,
   enddate DATE,
   cost NUMBER)
   ON COMMIT PRESERVE ROWS;
  
 Ex:
  CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER)
  ON COMMIT DELETE ROWS;
 
 Ex:Using Dynamic SQL creating global temp table
  BEGIN
  EXECUTE immediate 'CREATE GLOBAL TEMPORARY TABLE xx.xx_temp_table   
  (booking_id                     NUMBER, 
   name                           VARCHAR2(100))
  ON COMMIT PRESERVE ROWS';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  / 
 
 Ex:Using Dynamic SQL Drop global temp table
  BEGIN
  EXECUTE immediate 'drop table xx.xx_temp_table';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  /