Oracle 死鎖快速排查與殺進程實戰指南 (V$ 視圖查詢 + ALTER SYSTEM KILL SESSION)

Oracle 死鎖/阻塞會話 4 步驟快速定位與解除

當應用程式卡住、資料庫回應緩慢，常因阻塞鎖 (Blocking Lock) 或死鎖。本文提供標準排查流程：從 V$DB_OBJECT_CACHE 找被鎖物件 → V$ACCESS 找持有會話 → V$SESSION/V$PROCESS 取得 SID/SERIAL#/SPID → ALTER SYSTEM KILL SESSION + OS ORAKILL 徹底清除。

步驟 1：找出被鎖定的程式物件 (Procedure/Package)

SELECT * FROM V$DB_OBJECT_CACHE
WHERE OWNER = '<物件擁有者>' AND LOCKS != '0';

記下 NAME (物件名)、TYPE (PROCEDURE/PACKAGE/PACKAGE BODY)。

步驟 2：找出持有該物件的會話 SID

SELECT * FROM V$ACCESS
WHERE OWNER = '<物件擁有者>' AND NAME = '<步驟1查到的物件名>';

結果欄位 SID 即為持有鎖的會話 ID。

步驟 3：取得完整會話資訊 (SID, SERIAL#, OS Process ID)

-- 取得 SERIAL# 與 PADDR
SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE SID = '<步驟2的SID>';

-- 取得作業系統 Process ID (SPID)
SELECT SPID FROM V$PROCESS WHERE ADDR = '<上一步查到的PADDR>';

記錄：SID, SERIAL#, SPID (Windows 用 ORAKILL 參數)。

步驟 4：殺掉會話 (兩階段確保徹底)

-- 1. 資料庫層級殺會話 (標記為 killed，由 PMON 清理)
ALTER SYSTEM KILL SESSION ',';

-- 2. 作業系統層級殺進程 (若資料庫層級無法立即釋放資源)
-- Windows:
ORAKILL  
-- Linux/Unix:
kill -9 

重要：若有多個阻塞會話，**需全部記錄下來逐一殺掉**，遺漏任何一個都可能導致鎖鏈持續。

進階：直接查阻塞鏈 SQL (一條查到底)

SELECT 
    blocking_session,
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.sql_id,
    sq.sql_text,
    p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

此查詢直接顯示「誰被誰阻塞」、執行的 SQL、OS 進程 ID，可一次處理多條阻塞鏈。

常見誤區與注意事項

• KILL SESSION 非立即生效：會話狀態變 KILLED，PMON 背景程序非同步清理，大交易回滾需時間。
• ORAKILL 需管理員權限：Windows 必須以 Administrator 執行 CMD。
• 死鎖 vs 阻塞：Oracle 自動偵測死鎖 (ORA-00060) 並回滾受害者；阻塞則需人工介入。
• 生產環境謹慎操作：殺關鍵會話可能導致應用報錯、資料不一致，建議先通知應用擁有者。

預防措施

1. 應用端設定合理的 LOCK TIMEOUT / STATEMENT TIMEOUT
2. 統一存取順序 (如固定表順序) 避免循環等待
3. 長交易拆分、縮短持鎖時間
4. 監控腳本定期查詢 V$SESSION_WAIT 事件 enq: TX - row lock contention