查表被谁锁住的脚本

最新推荐文章于 2022-04-18 20:10:55 发布

转载最新推荐文章于 2022-04-18 20:10:55 发布 · 921 阅读

文章标签：

#user #session #null #table #library #脚本

Oracle 专栏收录该内容

538 篇文章

订阅专栏

本文提供了一个SQL脚本，用于查询当前锁定数据库表的会话及其等待的会话详情。脚本通过连接多个视图获取阻塞者与被阻塞者的用户名、进程ID等信息，并展示锁类型、请求模式等关键数据。

该脚本可以查现在谁锁住表，以及谁在等待该表。

--
-- 查现在谁锁住表，以及谁在等待该表。
-- lock.sql
-- pURPLEfox
--
SELECT /*+ choose */
      bs.username "Blocking User", bs.username "DB User",
      ws.username "Waiting User", bs.sid "SID", ws.sid "WSID",
      bs.serial# "Serial#", bs.sql_address "address",
      bs.sql_hash_value "Sql hash", bs.program "Blocking App",
      ws.program "Waiting App", bs.machine "Blocking Machine",
      ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
      ws.osuser "Waiting OS User", bs.serial# "Serial#",
      ws.serial# "WSerial#",
      DECODE (
         wk.TYPE,
         'MR', 'Media Recovery',
         'RT', 'Redo Thread',
         'UN', 'USER Name',
         'TX', 'Transaction',
         'TM', 'DML',
         'UL', 'PL/SQL USER LOCK',
         'DX', 'Distributed Xaction',
         'CF', 'Control FILE',
         'IS', 'Instance State',
         'FS', 'FILE SET',
         'IR', 'Instance Recovery',
         'ST', 'Disk SPACE Transaction',
         'TS', 'Temp Segment',
         'IV', 'Library Cache Invalidation',
         'LS', 'LOG START OR Switch',
         'RW', 'ROW Wait',
         'SQ', 'Sequence Number',
         'TE', 'Extend TABLE',
         'TT', 'Temp TABLE',
         wk.TYPE
      ) lock_type,
      DECODE (
         hk.lmode,
         0, 'None',
         1, 'NULL',
         2, 'ROW-S (SS)',
         3, 'ROW-X (SX)',
         4, 'SHARE',
         5, 'S/ROW-X (SSX)',
         6, 'EXCLUSIVE',
         TO_CHAR (hk.lmode)
      ) mode_held,
      DECODE (
         wk.request,
         0, 'None',
         1, 'NULL',
         2, 'ROW-S (SS)',
         3, 'ROW-X (SX)',
         4, 'SHARE',
         5, 'S/ROW-X (SSX)',
         6, 'EXCLUSIVE',
         TO_CHAR (wk.request)
      ) mode_requested,
      TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
      DECODE (
         hk.BLOCK,
         0, 'NOT Blocking',          /* Not blocking any other processes */
         1, 'Blocking',             /* This lock blocks other processes */
         2, 'Global',          /* This lock is global, so we can't tell */
         TO_CHAR (hk.BLOCK)
      )
            blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
   AND hk.lmode != 0
   AND hk.lmode != 1
   AND wk.request != 0
   AND wk.TYPE(+) = hk.TYPE
   AND wk.id1(+) = hk.id1
   AND wk.id2(+) = hk.id2
   AND hk.sid = bs.sid(+)
   AND wk.sid = ws.sid(+)
   AND (bs.username IS NOT NULL)
   AND (bs.username <> 'SYSTEM')
   AND (bs.username <> 'SYS')
ORDER BY 1
/