The following queries are based on
this excellent post.
/* Who's blocking who */
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;
/* Which object and row is blocked */
SELECT SID, 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 s.row_wait_obj# = DO.object_id
AND SID IN (
SELECT DISTINCT s2.SID
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);