Oracle 死鎖進程精準定位與雙層殺除實戰：V$DB_OBJECT_CACHE → V$SESSION → OS KILL

死鎖現象與風險

PL/SQL 程序單元（過程/函數/包/觸發器）被會話鎖定（PIN/LOCK），導致無法編譯（ORA-04021）、無法執行、甚至阻塞業務。需精準定位持有鎖的會話並強制釋放。

四步定位法

1. 查找被鎖的程序對象

SELECT * FROM V$DB_OBJECT_CACHE
WHERE OWNER = '目標用戶'
  AND TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
  AND LOCKS <> '0';  -- 或 PINS <> '0'

記錄 NAME（對象名）、NAMESPACE。

2. 定位持有鎖的會話 SID

SELECT * FROM V$ACCESS
WHERE OWNER = '目標用戶'
  AND NAME = '上一步查到的對象名';

結果包含 SID（會話標識）。

3. 獲取 SID 對應的 SERIAL# 與 OS 進程號 SPID

-- 獲取 SERIAL# 與 PADDR
SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE SID = 上一步的SID;

-- 獲取 OS 進程號 SPID
SELECT SPID FROM V$PROCESS WHERE ADDR = 上一步的PADDR;

4. 雙層殺除：先數據庫層，後 OS 層

-- 步驟 1：標記會話終止（釋放鎖、回滾事務）
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
-- 例：ALTER SYSTEM KILL SESSION '131,33574';

若返回 ORA-00031: session marked for kill，說明會話正在清理中，但資源未立即釋放。

-- 步驟 2：OS 層強制終止（徹底釋放共享內存結構鎖）
-- Linux/Unix
kill -9 SPID
-- 例：kill -9 12345

-- Windows (需 Oracle Home/bin 下的 orakill.exe)
orakill SID SPID
-- 例：orakill ORCL 12345

驗證釋放

-- 再次查詢 V$DB_OBJECT_CACHE，LOCKS 應為 0
-- 嘗試編譯該程序，應成功

進階：批量腳本（針對某用戶所有被鎖程序）

SET SERVEROUTPUT ON
DECLARE
  v_sql VARCHAR2(500);
BEGIN
  FOR r IN (
    SELECT s.SID, s.SERIAL#, p.SPID, o.NAME
    FROM V$DB_OBJECT_CACHE o
    JOIN V$ACCESS a ON o.OWNER = a.OWNER AND o.NAME = a.NAME
    JOIN V$SESSION s ON a.SID = s.SID
    JOIN V$PROCESS p ON s.PADDR = p.ADDR
    WHERE o.OWNER = 'TARGET_USER'
      AND o.LOCKS <> '0'
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Killing ' || r.NAME || ' SID=' || r.SID);
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.SID || ',' || r.SERIAL# || '''';
    -- OS 層需手工或通過外部調度執行 kill -9 / orakill
  END LOOP;
END;
/
-- 注意：OS 層殺進程極具破壞力，生產環境務必人工確認後執行！

預防建議

• 避免長事務持有 PL/SQL 鎖：批量操作分批提交。
• 部署階段鎖定策略：ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2 減少無效編譯。
• 監控告警：針對 V$DB_OBJECT_CACHE.LOCKS > 0 持續 > 5 分鐘發送告警。