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

Question

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

Answer 1

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

Answer 2

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.