Tuesday, January 25, 2011

ORA-14450: attempt to access a transactional temp table already in use

While DDL(alter table ....) on a Global Temporary Table I encountered "ORA-14450".
Then I searched in V$LOCKED_OBJECTS but didn't find anything there(If I am not wrong,V$LOCKED_OBJECTS contains only DML related locks ). The I fired below query to find the session locking my TEMP Table:

SELECT s.INST_ID,
o.object_name,
s.sid,
s.STATUS,
s.serial#,
s.username,
s.osuser,
s.machine,
'alter system kill session ''' || to_char(s.sid) || ',' ||
to_char(s.serial#) || ''';' ks
FROM dba_objects o, gv$lock a, gv$session s
WHERE o.object_name = ''
AND o.owner = ''
AND a.id1 = o.object_id
AND a.type = 'TO'
AND a.sid = s.sid;

For the description of type column we can user below query:

select type,name,description from v$lock_type where type='TO';

Thanks to http://www.oracleoverflow.com/questions/266/alter-temporary-table-throws-ora-14450

3 comments:

  1. Ajudou muito, obrigado.

    Marlon.

    ReplyDelete
  2. Thank you for this. This was extremely helpful and knowledgeable!

    ReplyDelete