Friday, February 17, 2012

Global Temporary Tables (Part 3)

Tested on Oracle 9. If you create a global temporary table in one session, which I have shown in red:
 
SQL> create global temporary table gtt
  2  (col1 varchar2(10))
  3  on commit preserve rows
  4  /
 
Table created.
 
SQL> insert into gtt values ('Andrew')
  2  /
 
1 row created.
 
SQL>
 
And you try to modify it in another session, which I have shown in blue:
 
SQL> alter table gtt modify
  2  (col1 varchar2(15))
  3  /
alter table gtt modify
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp
table already in use
 
SQL>
 
You will get an ORA-14450 message. You can diagnose this as follows:
 
SQL> l
  1  select sid from v$lock
  2  where id1 =
  3  (select object_id from user_objects
  4*  where object_name = 'GTT')
SQL> /
 
       SID
----------
         7
 
SQL>
 
This gives you the SID of the session holding the lock and you can then get further information in v$session.

No comments: