查询oracle中的死锁

第一步：找到死锁的sid

select l1.sid sid1,'is blocking' action ,l2.sid sid2,round(l1.ctime/60)||'min' ctime1,
round(l2.ctime/60)||'min' ctime2, s.username, o.object_name, s.sql_id
from gv$lock l1, gv$lock l2, v$session s, dba_object o
where l1.block=1 and l1.id1=l2.id1 and l1.id2=l2.id2 and l1.sid=s.sid and l1.id1=o.object_id(+)
and l1.sid<>l2.sid --and round(l1.ctime/60)>3 --阻塞超过3分钟

第二步：根据sid找到TM类型的锁，TM类型锁对应的ID1为dba_objects中的object_id

select * from gv$lock where sid=sid

第三步：找到锁住的对象

select * from dba_objects where object_id= objid

ITPub博客

查询oracle中的死锁

最新文章