Global Temporary Tables

Temporary tables were introduced in Oracle 8i. There are two types of temporary tables, GLOBAL TEMPORARY and TEMPORARY.

• a GLOBAL TEMPORARY table is one whose data is visible to all sessions
   a TEMPORARY table has contents only visible to the session that is using it

A temporary table can have session-specific or transaction specific data depending on how the ON COMMIT clause is used in the table's definition. The temporary table doesn't go away when the session or sessions are finished with it; however, the data in the table is removed.
Temporary tables do not have the same redo generation as other tables. The data in the tables are temporary and do not have to be rebuilt in the event of a failure.
Here is an example creation of both a preserved and deleted temporary table:

SQL>   CREATE GLOBAL TEMPORARY TABLE test6 (
  2     starttestdate DATE,
  3     endtestdate DATE,
  4     results NUMBER)
  5* ON COMMIT PRESERVE ROWS
SQL> /

Table created.

SQL> desc test6
 Name                            Null?    Type
 ------------------------------- -------- ----
 STARTTESTDATE                            DATE
 ENDTESTDATE                              DATE
 RESULTS                                  NUMBER


SQL> CREATE GLOBAL TEMPORARY TABLE test7 (
  2     starttestdate DATE,
  3     endtestdate DATE,
  4     results NUMBER)
  5  ON COMMIT DELETE ROWS
  6  /

Table created.

SQL> desc test7
 Name                            Null?    Type
 ------------------------------- -------- ----
 STARTTESTDATE                            DATE
 ENDTESTDATE                              DATE
 RESULTS                                  NUMBER


SQL> insert into test6 values (sysdate, sysdate+1, 100);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test7 values (sysdate, sysdate+1, 100);

1 row created.

SQL> select * from test7;

STARTTEST ENDTESTDA    RESULTS
--------- --------- ----------
29-MAR-99 30-MAR-99        100

SQL> commit;

Commit complete.

SQL> select * from test6;

STARTTEST ENDTESTDA    RESULTS
--------- --------- ----------
29-MAR-99 30-MAR-99        100

SQL> select * from test7;

no rows selected

SQL> 

The items to notice in the example above are:

• With the PRESERVE option, the data is kept after a commit while with the DELETE option the data is removed from the table when a COMMIT occurs.
   
• Even with the GLOBAL option set and select permission granted to public on the temporary table we couldn't see the data in that table from another session. However, we could however perform a DESCRIBE on the table and insert values into it, which then the owner of the temporary table couldn't see.