oracle 互锁的sql查询

SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
       id1,
       id2,
       lmode,
       request,
       type
  FROM V$LOCK
 WHERE (id1, id2, type) IN
       (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
 ORDER BY id1, request;

select blkingsess.sid blockingsid, blkedsess.sid blockedsid
  from v$lock blkingsess,
       (select * from v$lock where request != 0) blkedsess
 where blkingsess.id1 = blkedsess.id1
   and blkingsess.id2 = blkedsess.id2
   and blkingsess.sid != blkedsess.sid;

select s.sql_id, s.sql_text, v.sid, v.machine
  from v$sql S, v$session V
 where s.address = v.sql_address
   and v.sid in (select blkedsess.sid blockedsid
                   from v$lock blkingsess,
                        (select * from v$lock where request != 0) blkedsess
                  where blkingsess.id1 = blkedsess.id1
                    and blkingsess.id2 = blkedsess.id2
                    and blkingsess.sid != blkedsess.sid)
union
select s.sql_id, s.sql_text, v.sid, v.machine
  from v$sql S, v$session V
 where s.address = v.sql_address
   and v.sid in (select blkingsess.sid blockingsid
                   from v$lock blkingsess,
                        (select * from v$lock where request != 0) blkedsess
                  where blkingsess.id1 = blkedsess.id1
                    and blkingsess.id2 = blkedsess.id2
                    and blkingsess.sid != blkedsess.sid);
;

转载于:https://www.cnblogs.com/seasonzone/p/3761545.html