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
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
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.
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.