When a session is waiting for "enq: TX - row lock contention", how to find the blocking session and object and row it is waiting for...
To find who is blocking ?
Waiting for object and rowid ...
To find who is blocking ?
- Code:
select sb.username || '@' || sb.machine|| ' ( SID=' || sb.sid || ' ) is blocking '|| sw.username || '@' || sw.machine || ' ( SID=' || sw.sid || ' ) ' AS blocking_status
from v$lock lb, v$session sb, v$lock lw, v$session sw
where sb.sid=lb.sid and sw.sid=lw.sid and lb.BLOCK=1 and lw.request > 0 and lb.id1 = lw.id1 and lw.id2 = lw.id2 ;
Waiting for object and rowid ...
- Code:
select o.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects o where sid=&waiting_sid and s.ROW_WAIT_OBJ# = o.OBJECT_ID ;
- Code:
select * from table_name_from_above where rowid =&rowid_returned