Create Temporary Tables in Oracle

Global Temporary Tables in Oracle

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

*********************************************************************************

HERE is an example to create a global temporary table with on commit DELETE ROWS :

*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT DELETE ROWS;

      

      

    

sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

sql> select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql> select * from ADMIN_WORK_AREA;

NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.

*********************************************************************************

HERE is an example to create a global temporary table with on commit PRESERVE ROWS :

*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT PRESERVE ROWS;

      

            

sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

1 row inserted.

sql>  select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql>  select * from ADMIN_WORK_AREA;

NOW exit the session and login back and select the table.

sql>  select * from ADMIN_WORK_AREA;

table is empty

NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows. 

This is equivalent to truncating table on session exit.