Display blocking locks

[PeterW]

Can somebody tell me what the best way is for displaying blocking locks in PLSQL Developer? I try to use the Sessions-window when a blocking lock occurs, but I am not sure which column displays relevant information (I'm no DBA!).

I have tried to create a new filter on the sessions window (Define Session Queries), but the result is very slow, and I'm not sure if it is correct.

Any ideas?

 

[ScottMattes]

I think that I got these from someone here:

This is what I use for blocking locks

--Locks (holding):
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
o.owner as owner_or_type, o.object_Name as NAME
from v$lock l, sys.all_objects o
where type = 'TM'
and l.sid = :sid
and o.object_id = l.id1
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, '4 WAIT for ITL free',
5, 'S/Row-X (SSX)',
6, '6 ITL entry',
to_char(lmode)) held,
decode(request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(request)) requested,
'ROLLBACK' as owner_or_type, r.name as NAME
from v$lock l, v$rollname r
where type = 'TX'
and l.sid = :sid
and r.usn = trunc(l.ID1/65536)
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'LOCK NAME' as owner_or_type, nvl(al.name, l.id1) as NAME
from v$lock l, sys.dbms_lock_allocated al
where type = 'UL'
and l.sid = :sid
and al.lockid (+) = l.id1

This is what I use for locks I'm holding

-- Locks (blocked by - slow on big systems):
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'no',
1, 'BLOCKED',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'BY SID' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and request <> 0)
and block = 1
and sid <> :sid
union all
select /*+ ORDERED */ ctime,type,
decode(block,
0, 'no',
1, 'BLOCKING',
2, 'Global',
to_char(block)) blocking,
decode(lmode,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(lmode)) held,
decode(request,
0, '0 None',
1, '1 Null',
2, '2 Row-S (SS)',
3, '3 Row-X (SX)',
4, '4 Share',
5, '5 S/Row-X (SSX)',
6, '6 Exclusive',
to_char(request)) requested,
'SID WAITING ON ME' as owner_or_type, to_char(SID) as NAME
from v$lock
where (id1, id2) in (select id1, id2 from v$lock where sid = :sid and block = 1)
and request <> 0
and sid <> :sid

 

[PeterW]

Thanks, Scott. I'll go and see if this works for me.

Nice weekend!