OracleSource

The Oracle9i Server onwards enables you to create temporary tables. Temporary tables can improve performance significantly by holding temporary data for reuse within your transaction or session. A temporary table has the following properties:
• Temporary table data is only visible within its defined scope; the scope can be defined to be a session or a transaction.
• The definition of a global temporary table is visible to all sessions. In contrast, the definition of a local temporary table does not persist at the end of the session that creates it. Note: The Oracle9i Server currently does not support local temporary tables.
• Temporary table data is stored within the sort space used by the session. If sort space is not sufficient to accommodate the data, space is allocated in the user’s temporary tablespace.
• Indexes on temporary tables have the same scope and duration as the table they correspond to.
• Triggers and views can be defined on temporary tables. However, a view cannot be defined joining a temporary and a permanent table.
• The CREATE GLOBAL TEMPORARY TABLE AS SELECT command can be used to create a temporary table and insert data into it.
• Definitions of temporary tables can be exported and imported.

Creating Temporary Tables

The example shows the command to create a temporary table where rows are only visible within the transaction that created them.
The clauses that control the duration of the rows are:
• ON COMMIT DELETE ROWS to specify that rows are only visible within the transaction (This is the default behavior).
• ON COMMIT PRESERVE ROWS to specify that rows are visible for the entire session.

Although the command does not create extents for the table, a session can query the table without receiving a error.
The view DBA_TABLES contains columns to indicate that the table is a temporary table. The column DURATION shows whether data in the table persists for a whole session or only within the transaction.

Note :There is a bug in 8.1.5 that shows the DURATION to be SYS$TRANSACTION even if the table is defined with ON COMMIT PRESERVE ROWS. The data in the table, however, is visible throughout the session.

SQL> CREATE GLOBAL TEMPORARY TABLE emp_temp
2 ( emp_id number(7)
3 , last_name varchar2(24)
4 , salary number(7,2) )
5 ON COMMIT DELETE ROWS;

SQL> select table_name, temporary, duration
2 from dba_tables
3 where table_name = 'EMP_TEMP';
TABLE_NAME TEMPORARY DURATION
-------------------- ------------------ ---------------
EMP_TEMP Y SYS$TRANSACTION