find blocking locks in oracle

It has been easier to find blocking locks from 10g onwards as v$session has blocker session id and blocker instance number.

select sid waiter_sid ,sql_id waiting_sql_id ,  blocking_session,blocking_instance
from v$session
where blocking_session is not null;

Before 10g you would query DBA_WAITERS and DBA_BLOCKERS to fnd waiter and blocker sessions.

Other way before 10g when you do not have above views or do not want to run script utllockt.sql then you would need to rely on V$LOCK

select
a.sid ,
(select serial# from v$session c where a.sid = c.sid) srl,
(select substr(machine,1,8) from v$session c where a.sid = c.sid) machine,
(select substr(program,1,11) from v$session c where a.sid = c.sid) program,
' is blocking ',
b.sid,
(select serial# from v$session c where b.sid = c.sid) srl,
(select substr(machine,1,8) from v$session c where b.sid = c.sid) machine,
(select substr(program,1,11) from v$session c where b.sid = c.sid) program
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
       SID        SRL MACHINE  PROGRAM     'ISBLOCKING'         SID        SRL MACHINE  PROGRAM
---------- ---------- -------- ----------- ------------- ---------- ---------- -------- -----------
       442         17 UAT-DB   JDBC Thin C  is blocking         394      21980 UAT-DB   JDBC Thin C
       442         17 UAT-DB   JDBC Thin C  is blocking         479         20 UAT-DB   JDBC Thin C

Alternate :

SELECTvh.SID locking_sid, vs.status status, vs.program program_holding, vw

.SID waiter_sid, vsw.program program_waiting

FROM v$lock vh, v$lock vw, v$session vs, v$session vsw

WHERE (vh.id1, vh.id2) IN (SELECT id1, id2

FROM v$lock

WHERE request = 0

INTERSECT

SELECT id1, id2

FROM v$lock

WHERE lmode = 0)

AND vh.id1 = vw.id1

AND vh.id2 = vw.id2

AND vh.request = 0

AND vw.lmode = 0

AND vh.SID = vs.SID

AND vw.SID = vsw.SID;

Additionally you can add criteria block=1 for blocker row from v$lock

Find the particular row for which there is row level lock contention:

select *
from &1..&2
where rowid =
        dbms_rowid.rowid_create (
                rowid_type      =>  1,
                object_number   => &3,
                relative_fno    => &4,
                block_number    => &5,
                row_number      => &6
        )
/

Pass it schema name, Object name,Object number, relative_fno , block# and row# . You can find these parameter values from v$session for sid which is blocked.