Oracle Locks - What’s blocking my lock?

There’s more details at orafaq.com
The main useful queries are:

List each session id which is blocking another lock:

select l1.sid as blockingsid, ’ IS BLOCKING ‘, l2.sid as blockedsid
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;

A quicker query

Sometimes the above query can take an age to run, so try the below set of 3 queries, the id1 links each lock together, the SID where block=1 is the blocking sid and the request>0 is where the SID is being blocked.

select * from v$lock where block>0 or request>0 order by id1;
select * from v$lock where block>0 or request>0 order by sid;
select * from v$session where sid=<INSERTSIDHERE>;

List the table and row being locked by a specified sid:

select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=INSERT_SID_HERE
and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

Tracing an active session

Login using sqlplus as sysdba (sqlplus ‘/ as sysdba’)
Run

select sid,serial# from v$session where sid=’sidyouwantotrace’;
exec sys.dbms_system.set_sql_trace_in_session(‘sid’,’serial#’,true);

Then look in /oracle/app/admin/dbname/udump
for a trace file
To analyse the tracefile, run tkprof /path/to/input.trc /path/to/output.txt