lock blocking

view name: v$lock

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

select l1.sid, ' IS BLOCKING ', l2.sid
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;

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.


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 ;

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

find object_id and info from v$session;

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=479 ;

generate rowid

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=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;




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


comment
I came upon your article that is excellent.
However, there is a small mistake about dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ):

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.

That means that you need to join with dba_objects to get the data_object_id and with dba_data_files to get the absolute file_id.