查找阻塞会话

命令

SELECT bl.sid       AS blocker_sid,
       bl.serial#   AS blocker_serial,
       bl.username  AS blocker_user,
       w.sid        AS waiter_sid,
       w.serial#    AS waiter_serial,
       w.username   AS waiter_user,
       w.seconds_in_wait AS wait_sec,
       w.event
FROM   v$session w
  JOIN v$session bl ON bl.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL
ORDER BY w.seconds_in_wait DESC;

作用

列出阻塞者与等待者会话对，以及等待时间和事件。

适用场景

用于在“应用挂起 / enq: TX 等待”之类的锁问题中找出元凶。

注意

终止阻塞者之前先与业务负责人确认；它可能是关键操作。仅查询；无风险。