Oracle数据库检查死锁的sql

2007-06-29 作者:bitsCN整理来源:中国网管联盟点评 投稿 收藏

实现代码如下：

SELECT SID, DECODE (BLOCK, 0, 'NO', 'YES') blocker,

DECODE (request, 0, 'NO', 'YES') waiter 网管联盟bitsCN_com

FROM v$lock

WHERE request > 0 OR BLOCK > 0

ORDER BY BLOCK DESC;

网管bitscn_com

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\", 网管u家www.bitscn.net

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#\",

网管有家bitscn.net

DECODE (wk.TYPE,

'MR', 'Media Recovery',

'RT', 'Redo Thread', 网管朋友网www_bitscn_net

'UN', 'USER Name',

'TX', 'Transaction',

'TM', 'DML', 网管bitscn_com

'UL', 'PL/SQL USER LOCK',

'DX', 'Distributed Xaction',

'CF', 'Control FILE', 网管u家u.bitsCN.com

'IS', 'Instance State',

'FS', 'FILE SET',

'IR', 'Instance Recovery',

网管朋友网www_bitscn_net

'ST', 'Disk SPACE Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

网管u家u.bitsCN.com

'LS', 'LOG START OR Switch',

'RW', 'ROW Wait',

'SQ', 'Sequence Number', 网管论坛bbs_bitsCN_com

'TE', 'Extend TABLE',

'TT', 'Temp TABLE',

wk.TYPE

网管u家u.bitscn@com

) lock_type,

DECODE (hk.lmode,

0, 'None',

1, 'NULL',

中国网管论坛bbs.bitsCN.com

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE', 网管有家bitscn.net

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (hk.lmode)

网管联盟bitsCN_com

) mode_held,

DECODE (wk.request,

0, 'None',

1, 'NULL',

网管bitscn_com

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

网管有家www.bitscn.net

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (wk.request) 网管bitscn_com

) 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 */

中国网管联盟bitsCN.com

1, 'Blocking', /* This lock blocks other processes */

网管下载dl.bitscn.com

2, 'Global', /* This lock is global, so we can't tell */

网管u家www.bitscn.net

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

网管bitscn_com

AND hk.lmode != 1

AND wk.request != 0

AND wk.TYPE(+) = hk.TYPE

网管论坛bbs_bitsCN_com

AND wk.id1(+) = hk.id1

AND wk.id2(+) = hk.id2

AND hk.SID = bs.SID(+) 网管朋友网www_bitscn_net

AND wk.SID = ws.SID(+)

AND (bs.username IS NOT NULL)

AND (bs.username <> 'SYSTEM')

网管网www_bitscn_com

AND (bs.username <> 'SYS')

ORDER BY 1;

顶一下

TAGs：检查 数据库 AND DECODE TO_CHAR User bs.username hk.lmode

上一篇：详细介绍ORACLE sqlplus命令下一篇：详细讲述PLSQL的书写及语法

Oracle数据库检查死锁的sql

2007-06-29 作者:bitsCN整理 来源:中国网管联盟 点评 投稿 收藏

2007-06-29 作者:bitsCN整理来源:中国网管联盟点评投稿收藏