之前总结过一篇博客“ORA-14450: attempt to access a transactional temp table already in use”,里面的脚本仅适合于讲述如何解决ORA-14450错误,在生产环境中,肯定需要快速解决问题,你可以使用下面脚本kill_session_ora_14450.sql找出涉及临时表的会话,并生成kill session的脚本。
SET LINESIZE 1080; SET COL KILL_SESSION FOR A80; SELECT s.SID, s.SERIAL#, s.STATUS, s.PADDR, 'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# || ''' IMMEDIATE;' AS kill_cmd_text FROM V$SESSION s WHERE s.SID IN (SELECT SID FROM V$ENQUEUE_LOCK T WHERE T.TYPE = 'TO') AND s.SID IN(SELECT SID FROM V$LOCK WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = UPPER('&TABLE_NAME') AND OBJECT_TYPE = 'TABLE')); |
|