Temporary Tables

Even prior to Oracle 8i most applications have used temporary tables to store data that is too complicated to process in a single sweep. These tables may have been in the form of database tables or PL/SQL tables. In Oracle 8i the maintenance and management of such temporary tables is taken on by the server.

Creation Of Temporary Tables

The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If the data is to deleted at the end of the transaction the table should be defined as follows:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

If on the other hand that data should be preserved until the session ends it should be defined as follows:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Micellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Tempoarary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.