Checking blocking locks is regular task of Oracle DBA. But problem is that how can find out blocking locks from database. Because so many SQL scripts are available and some of those stuck after execution of script. Here I am providing some of good SQL scripts for finding block locks.
Script-1: Blocking lock find out
- Code: Select all
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;