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.

Oracle Autonomous Transactions

Autonomous transactions allow you to create a new transaction within a transaction that may commit or roll back changes, independently of its parent transaction. They allow you to suspend the currently executing transaction, start a new one, do some work and commit or roll back, all without affecting the currently executing transaction state.

The directive PRAGMA AUTONOMOUS_TRANSACTION tells the database that this procedure, when executed, is to be executed as a new autonomous transaction, independently of its parent transaction.

To ilustrate this by an example, let's create one logging table that will be used to log some actions within your package.

create table actions_log
( username varchar2(30),
timestamp date,
notes varchar2(1000)
);

Then your package will have an autonomous transaction procedure ..

create or replace package body my_pkg
as
procedure log( p_notes in varchar2)
as
pragma autonomous_transaction;
begin
insert into actions_log values
( user, sysdate, p_notes );
commit;
end;
...
...
log ('Action 1');
...
...
end;