A temporary table has a definition or structure that persists like that of a regular table, but the data it contains exists only for the duration of a transaction or session. Oracle8i allows you to create temporary tables to hold session-private data. You specify whether the data is specific to a session or to a transaction.

Here are a few examples of when temporary tables can be useful:

A Web-based airline reservations application allows you, as a customer, to create several optional itineraries. As you develop each itinerary, the application places the data in a row of a single temporary table. As you modify each itinerary, the application updates that row accordingly. When you ultimately decide which itinerary you want to use, the application moves the row for that itinerary to a persistent table.

During your session, the data you enter is private. When you end your session, the optional itineraries you developed are dropped.

Several sales agents for a large bookseller use a single temporary table concurrently while taking customer orders over the phone. To enter and modify customer orders, each agent accesses the table in a session that is unavailable to the other agents. When the agent closes a session, the data from that session is automatically dropped, but the table structure persists for the other agents to use.

An administrator uses temporary tables to improve performance when running an otherwise complex and expensive query. To do this, the administrator caches the values from a more complex query in temporary tables, then run SQL statements, such as joins, against those temporary tables.

You create a temporary table by using special ANSI keywords. You specify the data as session-specific by using the ON COMMIT PRESERVE ROWS keywords. You specify the data as transaction-specific by using the ON COMMIT DELETE ROWS keywords.

You can create indexes on temporary tables as you would on permanent tables.

For a session-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session.

For a transaction-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the transaction. The binding goes away at the end of the transaction.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

Temporary segments are de-allocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

You cannot create a table that is simultaneously both transaction- and session-specific.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of a temporary table's data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

The following statement creates a session-specific 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.