2. 查询锁等待和处理死锁脚本[@more@]2. 查询锁等待和处理死锁脚本
SELECT /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(b1) NO_MERGE(c) NO_MERGE(d) */ 'Lock' "Status",
a.username "用户名", a.sid "SID", a.serial# "SERIAL#",
b.type "锁类型",
DECODE(b.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', 'NONE') "占用的模式",
DECODE(b.request, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', 'NONE') "请求的模式",
c.object_name "对象名",
c.owner "对象所有者", c.object_type "对象类型",
b.id1 "资源ID1", b.id2 "资源ID2",
'ALTER SYSTEM KILL SESSION'''||a.sid||','||a.serial#||''';' "Kill Session",
'KILL -9 '||d.spid "Kill Process"
FROM v$session a, v$lock b, v$locked_object b1, dba_objects c, v$process d
WHERE a.type <> 'BACKGROUND'
AND EXISTS(SELECT /*+ NO_MERGE(e) NO_MERGE(f) */ 1 FROM v$session e, v$lock f WHERE e.lockwait = f.kaddr AND f.id1 = b.id1)
AND a.sid = b.sid
AND b.request = 0
AND d.addr = a.paddr
AND b1.session_id = a.sid
AND b1.object_id = c.object_id