Oracle 應用經驗談 (2)：監控 Session、鎖定與阻塞的實用查詢腳本

收集整理多條實戰常用的動態效能視圖查詢，協助 DBA 快速定位「誰在存取資料庫」、「誰被鎖住」、「誰在鎖表」以及具體爭用資源。

1. 查詢目前正在存取資料庫的 Session

SELECT c.sid, c.serial#, c.username, a.object_id, b.object_name,
       c.program, c.status, d.name, c.osuser
FROM   v$locked_object a, all_objects b, v$session c, audit_actions d
WHERE  a.object_id = b.object_id
  AND  a.session_id = c.sid(+)
  AND  c.command = d.action;

可搭配 ALTER SYSTEM KILL SESSION '&1,&2'; 強制中斷異常 Session。

2. 依使用者查詢 Session 詳細資訊

SELECT a.sid, a.serial#, a.username, a.status, a.program, b.name, a.osuser
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
  AND  username = '&1';

3. 查詢正在等待鎖定的 Session (被鎖住)

SELECT a.sid, a.serial#, a.username, a.lockwait, a.status, a.program, b.name
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
  AND  lockwait IS NOT NULL;

4. 查詢持有鎖定的 Active Session (誰在鎖表)

SELECT a.sid, a.serial#, a.username, a.lockwait, a.status, a.program, b.name
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
  AND  status = 'ACTIVE';

5. 定位具體被鎖定的物件與區塊

SELECT sid, serial#, object_name, row_wait_block#,
       row_wait_row#, row_wait_file#
FROM   all_objects, v$session
WHERE  row_wait_obj# = object_id
  AND  type = 'USER'
  AND  lockwait IS NOT NULL;

6. 透過 v$lock 找出阻塞鏈

SELECT sl.username, sl.sid, sl.serial#
FROM   v$lock l1, v$session s1
WHERE  EXISTS (SELECT *
               FROM   v$lock l2, v$session s2
               WHERE  l2.sid = s2.sid
                 AND  l2.id1 = l1.id1
                 AND  l2.id2 = l1.id2
                 AND  l2.request = 0
                 AND  l1.request > 0
                 AND  l1.sid = s1.sid);

此查詢可找出持有鎖定且導致其他 Session 等待的阻塞者。

使用建議

• 建立 Shell/PL/SQL 腳本封裝上述查詢，納入日常巡檢或告警機制。
• 搭配 V$SESSION_WAIT、V$SESSION_EVENT 進一步分析等待事件類型。
• 殺 Session 前務必確認業務影響，必要時協同開發人員處理。