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

oracle, transactions

Solution

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

Problem

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

Original source