http://www.orafaq.com/node/854

SQL from the link above URL to find blocking SQL
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;

Output:
BLOCKING_STATUS
——————————————————————————–
SYS@localhost.localdomain ( SID=19 ) is blocking SYS@localhost.localdomain ( SID=21 )

Find the object being locked
SQL> SELECT vl.session_id, do.object_name FROM V$LOCKED_OBJECT vl, dba_objects do
WHERE vl.session_id IN (19, 21) and vl.object_id = do.object_id;

SESSION_ID OBJECT_NAME
———— ——————
19 TEST
21 TEST

• General DBA
• Lock