Oracle 死鎖進程診斷與終結標準流程：V$ 視圖定位、ALTER SYSTEM KILL、OS 層強制清理

死鎖導致會話互相等待鎖資源，Oracle 會自動偵測並回滾其中一個交易 (ORA-00060)，但**持有鎖的阻塞會話可能殘留**，需人工介入終結。本文提供標準化 4 步驟診斷流程：鎖定物件識別 → SID/Serial# 定位 → 進程地址關聯 → 資料庫層 KILL + OS 層強制清理。

一、 死鎖 vs 阻塞：概念釐清

二、 標準 4 步驟診斷終結流程

步驟 1：識別被鎖物件 (V$DB_OBJECT_CACHE / V$LOCKED_OBJECT)

-- 方法 A: 查詢被鎖物件 (含物件類型、擁有者)
SELECT do.owner, do.object_name, do.object_type,
       lo.session_id, lo.oracle_username, lo.os_user_name,
       lo.locked_mode, lo.xidusn, lo.xidslot, lo.xidsqn
FROM   v$locked_object lo
JOIN   dba_objects do ON lo.object_id = do.object_id;

-- 方法 B: 查詢 Library Cache 鎖 (PL/SQL 包/觸發器編譯/執行衝突)
SELECT namespace, owner, name, type, locks, pins
FROM   v$db_object_cache
WHERE  locks > 0 OR pins > 0;

步驟 2：定位阻塞會話 SID / SERIAL# (V$SESSION / V$LOCK)

-- 核心查詢：阻塞鏈完整視圖
SELECT blocking_session, sid, serial#, username, machine, program,
       event, wait_class, seconds_in_wait, state,
       sql_id, prev_sql_id
FROM   v$session
WHERE  blocking_session IS NOT NULL
   OR  sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

-- 簡化版：僅顯示阻塞者與被阻塞者
SELECT s1.sid blocker_sid, s1.serial# blocker_serial, s1.username blocker_user,
       s2.sid waiter_sid, s2.serial# waiter_serial, s2.username waiter_user,
       s2.event wait_event, s2.seconds_in_wait
FROM   v$session s1
JOIN   v$session s2 ON s1.sid = s2.blocking_session
WHERE  s2.blocking_session IS NOT NULL;

步驟 3：關聯 OS 進程 ID (SPID) (V$PROCESS)

-- 由 SID 查 SPID (Linux/Unix PID)
SELECT s.sid, s.serial#, s.username, p.spid, p.program
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.sid IN (:blocker_sid, :waiter_sid);  -- 代入步驟 2 獲得的 SID

步驟 4：雙層終結 (資料庫層 → OS 層)

4.1 資料庫層優雅終結 (首選)

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

-- 範例
ALTER SYSTEM KILL SESSION '152, 8734' IMMEDIATE;

• IMMEDIATE (11g+)：立即回滾交易、釋放鎖、斷開連線，無需等待當前呼叫完成
• 無 IMMEDIATE：標記會話為 KILLED，待當前操作結束才真正釋放
• 執行後查詢 V$SESSION 確認狀態變為 KILLED 或消失

4.2 OS 層強制清理 (資料庫層無效/殘留時)

-- Windows 範例 (SID=ORCL, SPID=1234)
orakill ORCL 1234

三、 進階：鎖類型識別與業務含義

SELECT l.sid, s.serial#, s.username, l.type, l.id1, l.id2,
       l.lmode, l.request, l.block, l.ctime,
       DECODE(l.type,
         'TM', 'DML Enqueue (表鎖)',
         'TX', 'Transaction Enqueue (行鎖)',
         'UL', 'User Lock (DBMS_LOCK)',
         'ST', 'Space Transaction',
         'RT', 'Redo Thread',
         'CF', 'Control File',
         'Other') lock_type
FROM   v$lock l
JOIN   v$session s ON l.sid = s.sid
WHERE  l.block = 1 OR l.request > 0
ORDER  BY l.ctime DESC;

常見鎖類型速查

四、 完整實戰腳本：一鍵生成 KILL 指令

SET SERVEROUTPUT ON
DECLARE
  CURSOR c_blockers IS
    SELECT s.sid, s.serial#, s.username, p.spid, s.machine, s.program,
           s.logon_time, s.last_call_et
    FROM   v$session s
    JOIN   v$process p ON s.paddr = p.addr
    WHERE  s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- 資料庫層 KILL 指令 (IMMEDIATE) --');
  FOR r IN c_blockers LOOP
    DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE;  -- ' || r.username || '@' || r.machine || ' SPID=' || r.spid);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(CHR(10) || '-- OS 層強制清理 (Linux) --');
  FOR r IN c_blockers LOOP
    DBMS_OUTPUT.PUT_LINE('kill -9 ' || r.spid || '  # ' || r.username || ' SID=' || r.sid);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(CHR(10) || '-- OS 層強制清理 (Windows orakill) --');
  FOR r IN c_blockers LOOP
    DBMS_OUTPUT.PUT_LINE('orakill ' || SYS_CONTEXT('USERENV','INSTANCE_NAME') || ' ' || r.spid || '  # ' || r.username);
  END LOOP;
END;
/

五、 事後驗證與預防

1. 確認阻塞鏈消失：重跑步驟 2 查詢無輸出
2. 檢查 Alert Log 是否有 ORA-00060 死鎖追蹤檔 (在 USER_DUMP_DEST)，分析 SQL 與索引設計
3. 常見預防措施：
   • 外鍵欄位**必建索引** (避免 TM 鎖升級)
   • 統一存取順序 (避免交易 A 鎖表 1→2，交易 B 鎖表 2→1)
   • 縮短交易長度：提前 COMMIT、避免交易中等待用戶輸入/遠端呼叫
   • 設定 DISTRIBUTED_LOCK_TIMEOUT / LOCK_TIMEOUT (12c+) 防止無限等待

六、 避坑指南

1. ❌ 直接 `kill -9` 不先嘗試 `ALTER SYSTEM KILL SESSION` → 可能導致資料檔不一致、實例崩潰
2. ❌ 僅 KILL 被阻塞者 (Waiter) → 阻塞者 (Blocker) 仍持有鎖，新會話繼續被阻塞
3. ❌ 忽略 `IMMEDIATE` 參數 → 長跑 SQL 可能讓 KILL 掛起數小時
4. ✅ 先資料庫層優雅終結，確認無效再 OS 層強制
5. ✅ 記錄 KILL 操作時間/對象/操作人，納入變更管理
6. ✅ 事後必做死鎖追蹤檔分析，從根源優化索引/交易邏輯

死鎖自動偵測解決交易層衝突，但會話層殘留需人工清理。**V$LOCKED_OBJECT → V$SESSION → V$PROCESS → ALTER SYSTEM KILL SESSION IMMEDIATE → orakill/kill -9** 這套標準化流程，能將阻塞影響時間壓縮至分鐘級，並保留完整審計軌跡。

1. 上一篇文章： Oracle DATE 型別完整操作指南：格式轉換、時間成分、NLS 參數與 JSP 整合陷阱
2. 下一篇文章： Oracle 徹底清理重裝指南：13 步驟消除殘留避免安裝精靈閃退

Oracle 資料庫基礎

Oracle 多表視圖不可更新？INSTEAD OF 觸發器完美解決

破解多表視圖 DML 限制：INSTEAD OF 觸發器實戰

Oracle 使用者密碼管理：解鎖、變更與還原密碼實作

適用情境 使用者帳號被鎖定（ORA-28000）。 需以特

Oracle 用戶權限查詢與授權實操指南：從 session_privs 到 DBA 授權

前言 掌握用戶權限的查詢與授權是 Oracle DBA 的基

使用 PL/SQL Developer 將遠端 Oracle 資料庫遷移至本地端完整教學

開發測試常需將生產/測試環境的 Schema 匯入本地資料庫