Rem Name: LockBlock.sql
Rem Author: Dave Hunt
Rem
Rem This script shows who is holding a lock that other people are
Rem waiting for and who the waiters are.
Rem
Rem
prompt
prompt Gathering lock information...
set echo off
set feedback off
set pagesize 0
set linesize 150
ttitle off
col "Object" format a20
col "Holder" format a25
col "Waiter" format a25
col "Lock Type" format a20
prompt ...Countdown...8...
create table TempAllObjects as select * from All_Objects;
prompt ...7...
create index TempAllObjectsID on TempAllObjects (object_id);
prompt ...6...
create table TempVSession as select * from v$Session;
prompt ...5...
create index TempVSessionSID on TempVSession (sid);
prompt ...4...
create table TempVLock as select * from v$lock;
prompt ...3...
create index TempVLockSID on TempVLock(SID);
prompt ...2...
create index TempVLockID1 on TempVLock(ID1);
prompt ...1...
create index TempVLockType on TempVLock(Type);
prompt GO
prompt
select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',
'There are '||count(*)||' blocking locks:')
from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
where lh.id1 = o.object_id
and lh.id1 = lw.id1
and sh.sid = lh.sid
and sw.sid = lw.sid
and sh.lockwait is null
and sw.lockwait is not null
and lh.type = 'TM'
and lw.type = 'TM'
/
set pagesize 35
select distinct o.owner||'.'||o.object_name "Object"
,sh.osuser||':'||sh.username||'('||sh.sid||')' "Holder"
,sw.osuser||':'||sw.username||'('||sw.sid||')' "Waiter",
decode(lh.lmode
, 0, 'none'
, 1, 'null'
, 2, 'row share'
, 3, 'row exclusive'
, 4, 'share'
, 5, 'share row exclusive'
, 6, 'exclusive'
, 'unknown') "Lock Type"
from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
where lh.id1 = o.object_id
and lh.id1 = lw.id1
and sh.sid = lh.sid
and sw.sid = lw.sid
and sh.lockwait is null
and sw.lockwait is not null
and lh.type = 'TM'
and lw.type = 'TM'
/
drop table TempAllObjects;
drop table TempVSession;
drop table TempVLock;
prompt
set feedback on
Gathering lock information...
...Countdown...8...
...7...
...6...
...5...
...4...
...3...
...2...
...1...
GO
There is 1 blocking lock:
Object Holder Waiter
Lock Type
-------------------- -------------------------
------------------------- -------------
TEST.S_EMP TENFOLD\dhunt:TEST(7) TENFOLD\dhunt:DHUNT(10) row exclusive