Oracle 鎖表查詢與解鎖完整腳本庫

遭遇鎖等待時，需快速定位「誰鎖了誰、鎖什麼資源、該不該 Kill」。三套標準查詢腳本覆蓋所有場景。

腳本 1：誰持有鎖 (鎖持有者視角)

SELECT /*+ RULE */ 
  s.username,
  DECODE(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', l.type) AS lock_level,
  o.owner, o.object_name, o.object_type,
  s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser
FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
  AND l.id1 = o.object_id(+)
  AND s.username IS NOT NULL
ORDER BY s.sid;

腳本 2：誰在等待鎖 (等待者視角)

SELECT /*+ RULE */
  LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username AS user_name,
  o.owner, o.object_name, o.object_type,
  s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
ORDER BY o.object_id, l.xidusn DESC;

腳本 3：完整鎖資訊 (含 OS 進程、SQL、等待事件)

SELECT 
  lo.owner || '.' || lo.object_name AS locked_object,
  lo.subobject_name,
  se.machine,
  lo.session_id,
  se.serial#,
  pr.spid AS os_spid,
  (SELECT instance_name FROM v$instance) AS instance,
  lo.oracle_username,
  lo.os_user_name,
  lo.process,
  lo.object_id,
  lo.object_type,
  se.logon_time,
  se.program,
  se.status,
  se.lockwait,
  se.action,
  se.client_info
FROM (
  SELECT obj.owner, obj.object_name, obj.subobject_name, obj.object_id, obj.object_type,
         lk.session_id, lk.oracle_username, lk.os_user_name, lk.process
  FROM (
    SELECT * FROM all_objects 
    WHERE object_id IN (SELECT object_id FROM v$locked_object)
  ) obj, v$locked_object lk
  WHERE obj.object_id = lk.object_id
) lo,
(
  SELECT sid, serial#, lockwait, status,
         (SELECT spid FROM v$process WHERE addr = paddr) spid,
         program, action, client_info, logon_time, machine
  FROM v$session
) se
WHERE lo.session_id = se.sid
ORDER BY lo.session_id;

腳本 4：取得阻塞 Session 的完整 SQL

SELECT sql_text
FROM v$sqltext
WHERE address IN (SELECT sql_address FROM v$session WHERE sid = :blocker_sid)
ORDER BY piece;

解鎖指令 (由輕到重)

1. 正常結束：通知應用 Commit/Rollback。
2. Kill Session：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; (回滾未提交事務，釋放鎖)。
3. OS 強殺：kill -9 spid (Linux) / orakill SID spid (Windows) — **最後手段**，可能導致資料檔損壞需執行執行個體恢復。

自動化監控建議

-- 定時任務：偵測阻塞 > 60 秒發送告警
SELECT blocking_session, sid, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
  AND seconds_in_wait > 60;

避坑原則

• 優先嘗試聯繫阻塞 Session 擁有者正常結束。
• Kill Session 會觸發大量回滾，高併發時可能加劇爭用，評估影響後再執行。
• OS 強殺後必須檢查 Alert Log 確認執行個體恢復狀態。

收藏四支腳本 + 解鎖 SOP，鎖表事件從慌亂變得可控。