Oracle检查死锁的sql（转）

Oracle死锁检测SQL

最新推荐文章于 2024-10-24 22:23:23 发布

转载最新推荐文章于 2024-10-24 22:23:23 发布 · 62 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/chijianqiang/archive/2008/02/20/2041602.html

文章标签：

#数据库

本文提供了两种用于检测Oracle数据库中死锁情况的有效SQL查询。第一种查询通过V$LOCK视图来快速查找可能存在死锁的会话。第二种查询则提供更详细的信息，包括阻塞进程与等待进程的用户名、应用程序名等，帮助管理员迅速定位问题。

检查死锁的sql语句

SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER, DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK
WHERE REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;

SELECT 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;

转载于:https://www.cnblogs.com/chijianqiang/archive/2008/02/20/2041602.html