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

4

7

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

Jard answered 24/11, 2014 at 18:12 Comment(1)
Probably means your code is trying to drop the GTT. Stop doing that, you don't need to drop GTTs.Desolate
S
17

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

Sind answered 24/11, 2014 at 18:21 Comment(0)
G
5

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.

Gelid answered 25/2, 2016 at 15:49 Comment(0)
O
0

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

Oneida answered 23/6, 2020 at 19:52 Comment(0)
X
0

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.

Xenophanes answered 16/5, 2022 at 12:25 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.