Tutorials

prompt "Identify the blocking session"
prompt "================================================="

select * from v$lock ;

prompt ""
prompt ""
prompt ""
prompt "Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1."
prompt "Further, you can tell which session is being blocked by comparing the values in ID1 and ID2."
prompt "The blocked session will have the same values in ID1 and ID2 as the blocking session,"
prompt "and, since it is requesting a lock it's unable to get, it will have REQUEST > 0."
prompt ""
prompt ""

prompt "================================================="
prompt "Detailed View"
prompt "================================================="

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

prompt ""
prompt ""
prompt "================================================="
prompt "Readable View"
prompt "================================================="


select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;