Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    How to find the waiting object and row

    Share

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    How to find the waiting object and row

    Post  Admin on Thu Feb 11, 2010 11:40 pm

    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 ?

    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 ; 
    Waiting for row..
    Code:
    select * from table_name_from_above where rowid =&rowid_returned

      Current date/time is Sat Aug 19, 2017 7:54 pm