死锁查询
SELECT object_name, machine, s.sid, s.serial#
FROM gv
l
o
c
k
e
d
o
b
j
e
c
t
l
,
d
b
a
o
b
j
e
c
t
s
o
,
g
v
locked_object l, dba_objects o, gv
lockedobjectl,dbaobjectso,gvsession s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
死锁处理
declare cursor mycur is
select b.username,b.sid,b.serial#,logon_time
from v
l
o
c
k
e
d
o
b
j
e
c
t
a
,
v
locked_object a,v
lockedobjecta,vsession b
where a.session_id = b.sid order by b.logon_time;
newcount number;
begin
for cur in mycur
loop
select count(1) into newcount from v
l
o
c
k
e
d
o
b
j
e
c
t
a
,
v
locked_object a,v
lockedobjecta,vsession b
where a.session_id = b.sid and b.sid=cur.sid and b.serial#=cur.SERIAL# order by b.logon_time;
if newcount>=1 then
execute immediate ( ‘alter system kill session ‘’’||cur.sid || ‘,’|| cur.SERIAL# ||’’’ ');
end if;
end loop;
end;