Oracle检查死锁的sql(转)
来源:互联网 发布:守望者 法老王 知乎 编辑:程序博客网 时间:2024/06/05 21:13
1.
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;
2.
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;
- Oracle检查死锁的sql(转)
- sqlserver检查死锁的sql
- [SQL]启动SQLServer的死锁检查
- [SQL]启动SQLServer的死锁检查
- [SQL]启动SQLServer的死锁检查
- [SQL]启动SQLServer的死锁检查
- [SQL]启动SQLServer的死锁检查
- [SQL]启动SQLServer的死锁检查
- Oracle中检查死锁
- 如何检查oracle死锁
- SQL Server 检查死锁
- SQL Server 检查死锁
- Oracle 检查命中率的SQL
- Oracle 检查命中率的SQL
- Oracle 检查命中率的SQL
- Oracle 检查命中率的SQL
- Oracle 检查命中率的SQL
- Oracle 检查命中率的SQL
- sqlserver2000误操作数据恢复工具
- 【转帖】利用Java生成静态HTML页面
- 获得客户端的IP相关知识
- 关于Oracle数据库的死锁(转书摘)
- Project中资源平衡操作简单指导
- Oracle检查死锁的sql(转)
- Front Page Server Extensions 是什么?
- FrontPage Server Extensions 定义(国外的好文章)
- 如何用VFP判断文本文件的编码格式?
- rescue的使用
- Driver的prob的调用顺序
- 普元基于构件开发
- 应用层对SOCKET层的需求
- tomcat6的servlet异步处理comet技术