abughali.com IT and Business Blog by Mahmoud Abu-Ghali

Oracle’s Temporary Tables

Temporary tables are used to hold intermediate resultsets, either for the duration of a transaction or a session. The data held in a temporary table is only ever visible to the current session – no other session will ever see any other session's data, even if the current session COMMITs the data. Multi-user concurrency is not an issue with regards to temporary tables either, one session can never block another session by using a temporary table. Even if we 'lock' the temporary table, it will not prevent other sessions using their temporary table, temporary tables generate significantly less REDO then regular tables would.

Oracle's temporary tables are similar to temporary tables in other relational databases with the main exception being that they are 'statically' defined. You create them once per database, not once per stored procedure in the database. They always exist – they will be in the data dictionary as objects, but will always appear empty until your session puts data into them. The fact that they are statically defined allows us to create views that reference temporary tables, to create stored procedures that use static SQL to reference them, and so on.

Temporary tables may be session-based (data survives in the table across commits but not a disconnect/reconnect). They may also be transaction-based (data disappears after a commit).

Here is an example showing the behavior of both.

create global temporary table temp_table_session
 on commit preserve rows
 as
 select * from scott.emp;

The ON COMMIT PRESERVE ROWS clause makes this a session-based temporary table. Rows will stay in this table until my session disconnects or I physically remove them via a DELETE or TRUNCATE. Only my session can see these rows; no other session will ever see 'my' rows even after I COMMIT:

create global temporary table temp_table_transaction
 on commit delete rows
 as
 select * from scott.emp;

The ON COMMIT DELETE ROWS makes this a transaction-based temporary table. When your session commits, the rows disappear. The rows will disappear by simply giving back the temporary extents allocated to our table – there is no overhead involved in the automatic clearing of temporary tables.