Transaction‐Level Temporary Tables with Autonomous Transaction

If you are using GLOBAL TEMPORARY tables, one thing you must be aware of is the fact that a  transaction‐level temporary table cannot be used by more than one transaction, in a single session, at the same time. Temporary tables are managed at the session‐level, and when they are created in the mode that makes them ʹtransactionalʹ (on commit, delete rows), they can only be used by either the parent, or the child transaction, but not both.


rajesh@10GR2> create global temporary table gtt(x number) on commit delete rows;

Table created.

Elapsed: 00:00:00.09
rajesh@10GR2> create or replace procedure auto_proc1 as
  2     pragma autonomous_transaction;
  3  begin
  4     insert into gtt values (1);
  5     commit;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> create or replace procedure auto_proc2 as
  2     pragma autonomous_transaction;
  3  begin
  4     for x in (select * from gtt)
  5     loop
  6             null;
  7     end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> insert into gtt values(1);

1 row created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec auto_proc1;
BEGIN auto_proc1; END;

*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "RAJESH.AUTO_PROC1", line 4
ORA-06512: at line 1


Elapsed: 00:00:00.04
rajesh@10GR2> exec auto_proc2;
BEGIN auto_proc2; END;

*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "RAJESH.AUTO_PROC2", line 4
ORA-06512: at line 1


Elapsed: 00:00:00.03
rajesh@10GR2>

This is the error you will get if you attempt to use the same temporary table by both transactions.