Oracle會話鎖定排查與Kill Session實戰：從V$視圖到精準解鎖

業務現象

用戶反饋「卡住不動」、「無法更新某行」、應用報ORA-00054: resource busy。核心任務：定位阻塞鏈 → 識別持有者/等待者 → 決策殺會話/等待提交。

一、 鎖定全景掃描（單條SQL）

-- 經典阻塞鏈查詢（含鎖模式、對象名、SQL文本）
SELECT 
  s.sid, s.serial#, s.username, s.status, s.logon_time,
  l.type, l.lmode, l.request, l.id1, l.id2,
  o.object_name, o.object_type,
  sq.sql_text
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE l.request > 0  -- 等待者
   OR (l.request = 0 AND l.lmode > 0 AND EXISTS (
        SELECT 1 FROM v$lock l2 WHERE l2.id1 = l.id1 AND l2.id2 = l.id2 AND l2.request > 0
   ))  -- 持有者且被等待
ORDER BY s.logon_time;

二、 簡化版：快速找SID/SERIAL#

-- 僅顯示被阻塞會話及阻塞者
SELECT 
  blocking.sid block_sid, blocking.serial# block_serial,
  blocked.sid wait_sid, blocked.serial# wait_serial,
  blocking.username block_user, blocked.username wait_user,
  o.object_name, l.type, l.lmode, l.request
FROM v$lock l
JOIN v$session blocked ON l.sid = blocked.sid AND l.request > 0
JOIN v$lock l2 ON l.id1 = l2.id1 AND l.id2 = l2.id2 AND l2.request = 0
JOIN v$session blocking ON l2.sid = blocking.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type IN ('TM','TX','UL');

三、 鎖模式解碼

四、 Kill Session 決策樹

1. 確認業務影響：聯繫應用擁有者，評估事務回滾成本 vs 繼續等待成本。
2. 優雅嘗試：ALTER SYSTEM KILL SESSION 'sid,serial#'; → 標記為KILLED，會話下次檢查點退出，事務回滾。
3. 強制終止（慎用）：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; → 立即回滾並釋放資源，PMON清理。
4. OS級殺進程（極端）：kill -9 spid（從V$PROCESS.SPID獲取）→ 實例可能崩潰，需實例恢復，**嚴禁常規使用**。

五、 實戰演示

-- 1. 發現阻塞
SELECT * FROM v$session WHERE blocking_session IS NOT NULL;

-- 2. 定位持有者
SELECT sid, serial#, username, sql_id, blocking_session
FROM v$session WHERE sid = 146;  -- 假設阻塞者SID=146

-- 3. 優雅殺會話
ALTER SYSTEM KILL SESSION '146,21177';

-- 4. 驗證釋放
SELECT * FROM v$lock WHERE request > 0;  -- 應無記錄

六、 附錄：高頻SQL片段

1. 空值邏輯真值表

NULL AND FALSE -> FALSE
NULL AND TRUE  -> NULL
NULL OR FALSE  -> NULL
NULL OR TRUE   -> TRUE

2. 聚合函數忽略NULL

SELECT SUM(sal) FROM emp;  -- NULL不參與計算
-- 排序時 NULL 最大（ASC最後，DESC最前），且無法建立B*Tree索引

3. MERGE 語法（10g增強）

MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val
  DELETE WHERE (s.flag = 'D')  -- 10g新增：匹配後再過濾刪除
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

4. WITH 子查詢因子化（僅SELECT）

WITH dept_cost AS (
  SELECT dname, SUM(sal) dept_total
  FROM emp e JOIN dept d ON e.deptno = d.deptno
  GROUP BY dname
)
SELECT dname, dept_total
FROM dept_cost
WHERE dept_total > (SELECT SUM(dept_total)/3 FROM dept_cost);

5. 全局臨時表（GTT）

CREATE GLOBAL TEMPORARY TABLE temp_session (
  id NUMBER, data VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;  -- 會話級保留
-- 或 ON COMMIT DELETE ROWS; -- 事務級保留
-- 特性：結構持久，數據隔離，提交/結束自動清理，統計信息共享（12c+可私有）

6. NVL / NVL2 / NULLIF

NVL(expr1, expr2)      -- expr1為NULL返回expr2，否則expr1
NVL2(expr1, e2, e3)    -- expr1非NULL返回e2，NULL返回e3
NULLIF(expr1, expr2)   -- 相等返回NULL，不等返回expr1

總結

鎖定排查核心：V$LOCK + V$SESSION + DBA_OBJECTS 三表聯查。Kill Session首選IMMEDIATE避免長時間回滾阻塞其他會話。建立「阻塞告警 → 自動收集現場 → 人工決策 → 執行Kill → 驗證恢復」標準化流程，將MTTR壓縮至分鐘級。