CODEWITHSUNDEEP
oracle-databasetransactions
user3546978
user3546978

Reputation: 111

ORA-14450 "attempt to access a transactional temporary table alreadyin use" with no autonomous transactions

i cannot provide specific code because the solution is classified. I'd just like to ask if, in some situation, it is possible to have the error ORA-14450 "attempt to access a transactional temporary table already in use" without having any autonomous transactions or DDL code.

This only happens once in a while. The application uses both database procedures and oracle forms.

Thanks in advance

Upvotes: 7

Views: 50482

Answers (4)

Shekhar Nalawade
Shekhar Nalawade

Reputation: 127

Closing a live connection to that particular schema worked for me. This has happened multiples times when I am accessing global temporary table and trying to update its definition.

Upvotes: 0

Sudheer Godgeri
Sudheer Godgeri

Reputation: 1

An alter table to (gtt_table) had literally stalled for hours... I had literally given up -- then I used the above query given here -- used the query below -killed the involved sessions (note involved -- not necessarily blocking sessions ), got this solved.

SELECT 'alter system kill session '''||s.sid||','||s.serial#||',@'||s.inst_id|| ''' ;' FROM gv$lock l, gv$session s WHERE l.id1 = (SELECT object_id FROM all_objects WHERE owner = '&ownernam' AND object_name= '&tmptblname') and l.sid=s.sid and l.inst_id=s.inst_id

Upvotes: 0

mmmmmpie
mmmmmpie

Reputation: 3039

ORA-14450 means you have a blocking session on the temp table. Find the blocking session and kill it if need be. 

SELECT * FROM v$lock
  WHERE id1 = (SELECT object_id FROM all_objects WHERE owner = <schema_name> 
               AND object_name =<glb temp table>) --find which sessions lock the temp table

SELECT * FROM v$session WHERE sid =<above query result> --find their's sid and serial

ALTER SYSTEM KILL SESSION '<sid>,<serial>';

Credit

Upvotes: 17

Viktor
Viktor

Reputation: 51

IF your GTT is created using on COMMIT PRESERVE ROWS clause then it will still not help if there is no locking. Once a user does a DML on this table and completes the transaction, the table is still in use. It won't show up in the v$lock if transaction is committed.

Upvotes: 5

Related Questions