Oracle数据库检查死锁的sql-PLSQL

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

实现代码如下：

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

DECODE (request, 0, 'NO', 'YES') waiter

网管下载dl.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家u.bitsCN.com

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#\", 网管朋友网www_bitscn_net

DECODE (wk.TYPE,

'MR', 'Media Recovery',

'RT', 'Redo Thread', 网管网www_bitscn_com

'UN', 'USER Name',

'TX', 'Transaction',

'TM', 'DML', 网管下载dl.bitscn.com

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

'DX', 'Distributed Xaction',

'CF', 'Control FILE',

网管联盟bitsCN@com

'IS', 'Instance State',

'FS', 'FILE SET',

'IR', 'Instance Recovery',

网管bitscn_com

'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',

网管u家u.bitsCN.com

'TE', 'Extend TABLE',

'TT', 'Temp TABLE',

wk.TYPE 网管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_com

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

6, 'EXCLUSIVE',

TO_CHAR (hk.lmode)

中国网管论坛bbs.bitsCN.com

) mode_held,

DECODE (wk.request,

0, 'None',

1, 'NULL',

网管u家www.bitscn.net

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

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

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

6, 'EXCLUSIVE',

TO_CHAR (wk.request)

网管u家u.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 */

中国网管论坛bbs.bitsCN.com

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

网管网www_bitscn_com

2, 'Global', /* This lock is global, so we can't tell */ 网管网www.bitscn.com

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

网管u家u.bitsCN.com

AND hk.lmode != 1

AND wk.request != 0

AND wk.TYPE(+) = hk.TYPE

网管u家www.bitscn.net

AND wk.id1(+) = hk.id1

AND wk.id2(+) = hk.id2

AND hk.SID = bs.SID(+) 网管联盟bitsCN_com

AND wk.SID = ws.SID(+)

AND (bs.username IS NOT NULL)

AND (bs.username <> 'SYSTEM')

网管u家www.bitscn.net

AND (bs.username <> 'SYS')

ORDER BY 1;

顶一下

TAGs：检查 数据库 AND DECODE TO_CHAR

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

Oracle数据库检查死锁的sql-PLSQL

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

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