Oracle 被鎖定物件診斷與解鎖完整操作指南

前言：鎖定風暴下的冷靜處理

資料庫鎖定是並發控制的必然產物，但長時間阻塞會導致業務雪崩。本文提供從告警發現、鎖定鏈追蹤、會話終止到作業系統層面強制清理的標準化處理流程，並強調 RAC 環境下的跨節點診斷要點。

一、 源頭告警：DBA_OUTSTANDING_ALERTS

Enterprise Manager / Cloud Control 將持久阻塞寫入此視圖，可直接查詢：

SELECT * FROM dba_outstanding_alerts
WHERE message_type = 'BLOCKING_SESSION';

輸出包含 SESSION_ID、SERIAL#、阻塞持續時間、影響物件等關鍵資訊。

二、 鎖定鏈全景掃描

1. 當前被鎖物件

SELECT lo.object_id, o.object_name, o.object_type,
       lo.session_id, lo.locked_mode, lo.oracle_username, lo.os_user_name
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id
ORDER BY lo.session_id;

locked_mode 解碼：0=None, 1=Null, 2=Row-S (SS), 3=Row-X (SX), 4=Share, 5=S/Row-X (SSX), 6=Exclusive (X)。

2. 依物件名反查鎖會話

SELECT s.sid, s.serial#, s.username, s.status, s.osuser, s.machine, s.program,
       l.type, l.lmode, l.request, l.id1, l.id2
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
WHERE l.id1 = (SELECT object_id FROM dba_objects WHERE object_name = UPPER('YOUR_TABLE'))
  AND l.type IN ('TM','TX');

3. RAC 環境跨節點查詢

-- GV$ 視圖自動聚合所有節點
SELECT inst_id, sid, serial#, username, status, blocking_session, blocking_instance
FROM gv$session
WHERE blocking_session IS NOT NULL
ORDER BY inst_id, blocking_session;

關鍵：BLOCKING_INSTANCE 指出阻塞源頭節點，避免在錯誤節點殺會話。

三、 精準終止阻塞會話

1. 正常終止（推薦）

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

• IMMEDIATE：立即回滾未提交事務、釋放鎖、標記會話為 KILLED，客戶端收到 ORA-00028
• 無 IMMEDIATE：標記為待終止，等待會話下一次資料庫呼叫時生效

2. 作業系統層面強制清理（最後手段）

-- 1. 找出對應 OS Process ID
SELECT p.spid, s.osuser, s.program, s.sid, s.serial#
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.sid = :target_sid;

-- 2. 作業系統終止 (Linux/Unix)
kill -9 

-- Windows (需管理員權限)
orakill  

警示：kill -9 會導致 PMON 執行實例恢復，可能產生大量 REDO 回滾，僅在正常終止失效時使用。

四、 完整處理流程圖

1. 告警觸發 → 查 DBA_OUTSTANDING_ALERTS 獲取 SID/SERIAL#
2. 驗證阻塞鏈 → GV$SESSION.BLOCKING_SESSION 確認阻塞者與受害者
3. 業務確認 → 聯繫應用擁有者評估回滾影響（長事務回滾可能更久）
4. 優雅終止 → ALTER SYSTEM KILL SESSION ... IMMEDIATE
5. 驗證釋放 → 重查 V$LOCKED_OBJECT 確認鎖消失
6. 必要時 OS 強殺 → kill -9 SPID
7. 事後分析 → 檢視 SQL_TEXT、執行計畫、鎖升級原因，優化應用邏輯或索引

五、 預防性最佳實踐

• 索引外鍵欄位：避免子表 DML 導致父表全表鎖（TM 模式 4/5）
• 縮短事務：批次提交、避免事務中等待用戶輸入/外部呼叫
• 應用層重試邏輯：捕捉 ORA-00054/ORA-00060 自動退避重試
• DDL 線上化：使用 DBMS_REDEFINITION、ALTER INDEX ... REBUILD ONLINE
• 監控儀表板：V$SESSION_WAIT、DBA_BLOCKERS、AWR 「Top Blocking Sessions」

六、 常見誤區

鎖定處理的核心原則：快速診斷、謹慎決策、最小影響、事後復盤。建立標準化 Runbook，讓值班人員在壓力下也能按流程操作。