How to check locking status? (Oracle)

When you are called to help an application hang issue, you would like to know if there are any database sessions are blocked and what transactions are blocking them and who/which session/which machines are blocking them.

The following script would help you find out if there are any locks in the database and who is blocking them.

prompt ====================================================

prompt == Start of check_lock.sql

prompt ====================================================

prompt ====================================================

prompt == Check Locks notes

prompt == 

prompt == Who is blocking whom?

prompt == 

prompt ====================================================

select s1.username || ' '|| s1.machine 

|| ' ( SID=' || s1.sid || ' ) is blocking ' 

|| s2.username || ' '|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status , s2.event as wait_event

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 ; 

prompt ====================================================

prompt == End of check_lock.sql

prompt ====================================================