Oracle Session 阻塞查詢與解除腳本：兩種標準查法與 Kill 指令生成

Oracle Session 阻塞查詢與解除腳本：兩種標準查法與 Kill 指令生成

使用者 A 執行 DELETE 未提交，使用者 B 更新同筆資料被阻塞。本文提供兩種標準查詢腳本快速定位阻塞源頭，並自動生成 KILL SESSION 指令。

場景重現

-- Session A
DELETE FROM test WHERE object_id < 10;  -- 8 rows deleted, 未 COMMIT

-- Session B
UPDATE test SET flag = 'N' WHERE object_id < 10;  -- 阻塞等待

方法一：v$locked_object + v$session (直觀易懂)

SELECT t2.username, t2.sid, t2.serial#, t2.logon_time
FROM v$locked_object t1, v$session t2
WHERE t1.session_id = t2.sid
ORDER BY t2.logon_time;

結果範例

較早登入的 SID=14 為阻塞者，SID=139 為被阻塞者。

方法二：v$lock 自聯結 (識別阻塞鏈)

SELECT
  (SELECT username FROM v$session WHERE sid = a.sid) AS blocker,
  a.sid,
  ' is blocking ',
  (SELECT username FROM v$session WHERE sid = b.sid) AS blockee,
  b.sid
FROM v$lock a, v$lock b
WHERE a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2;

結果範例

自動生成 KILL SESSION 指令

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';' AS cmd
FROM v$session
WHERE username = 'LIHUILIN' AND sid = 14;

輸出

CMD
-----------------------------------------
ALTER SYSTEM KILL SESSION '14,87';

執行解除阻塞

ALTER SYSTEM KILL SESSION '14,87';

進階：遞迴查詢完整阻塞樹 (11g+)

SELECT
  r.root_sid,
  s.serial#,
  r.blocked_num,
  r.avg_wait_seconds,
  s.username,
  s.status,
  s.event,
  s.machine,
  s.program,
  s.sql_id,
  s.prev_sql_id
FROM (
  SELECT
    CONNECT_BY_ROOT sid AS root_sid,
    AVG(seconds_in_wait) AS avg_wait_seconds,
    COUNT(*) - 1 AS blocked_num
  FROM v$session
  START WITH blocking_session IS NULL
  CONNECT BY PRIOR sid = blocking_session
  GROUP BY CONNECT_BY_ROOT sid
  HAVING COUNT(*) > 1
) r, v$session s
WHERE r.root_sid = s.sid
ORDER BY r.blocked_num DESC, r.avg_wait_seconds DESC;

此查詢以 BLOCKING_SESSION 遞迴建樹，統計每棵阻塞樹的根節點阻塞會話數與平均等待時間，排序找出最嚴重的阻塞源頭。

關鍵提醒

• 殺 Session 會觸發交易回滾，可能產生大量 Undo/Redo，請評估影響
• RAC 環境需在正確 Instance 執行，或用 GV$ 視圖加 INST_ID
• 持有鎖最久/等待最久的 Session **不一定** 是阻塞根源，需看阻塞樹結構
• 死鎖 Oracle 會自動偵測並解除 (其中一個 Session 回滾)，無需人工介入