Oracle DDL 掛起與預存程序無法編譯：鎖定分析與殺 Session 完整流程

Oracle DDL 掛起與預存程序無法編譯：鎖定分析與殺 Session 完整流程

兩大經典 Hang 住場景：表 DDL 無法執行（如 ALTER TABLE ADD COLUMN）、預存程序 CREATE OR REPLACE 掛起。本文提供鎖定查詢 SQL、V$LOCK/X$KGLPN 分析、殺 Session/Process 完整步驟。

場景一：表 DDL 掛起

重現

-- Session 1
CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
SELECT * FROM TEST WHERE OBJECT_ID = 20 FOR UPDATE;  -- 持有 TX 鎖

-- Session 2
ALTER TABLE TEST ADD AA NUMBER;  -- HANG，等待 TM-X

鎖定查詢（找阻塞者）

SELECT BS.USERNAME "Blocking User", BS.SID, BS.SERIAL#, BS.PROGRAM, BS.MACHINE,
       WS.USERNAME "Waiting User", WS.SID "WSID", WS.PROGRAM, WS.MACHINE,
       WK.TYPE LOCK_TYPE, HK.LMODE MODE_HELD, WK.REQUEST MODE_REQUESTED,
       HK.ID1 LOCK_ID1, HK.ID2 LOCK_ID2, HK.BLOCK BLOCKING_OTHERS
FROM V$LOCK HK, V$SESSION BS, V$LOCK WK, V$SESSION WS
WHERE HK.BLOCK = 1
  AND HK.LMODE NOT IN (0,1)
  AND WK.REQUEST != 0
  AND WK.TYPE = HK.TYPE
  AND WK.ID1 = HK.ID1
  AND WK.ID2 = HK.ID2
  AND HK.SID = BS.SID
  AND WK.SID = WS.SID
  AND BS.USERNAME NOT IN ('SYS','SYSTEM');

輸出解讀與處理

-- Session 1：執行長時間運行的程序
CREATE OR REPLACE PROCEDURE P_TEST_PIN AS BEGIN DBMS_LOCK.SLEEP(3000); END;
EXEC P_TEST_PIN;

-- Session 2：嘗試覆蓋編譯
CREATE OR REPLACE PROCEDURE P_TEST_PIN AS BEGIN DBMS_LOCK.SLEEP(1000); END;
-- HANG，等待 Library Cache Pin

方法一：X$KGLPN 定位 Library Cache Pin 持有者

方法二：直接查持有者 SQL