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.