Oracle DBA FAQ

Would you like to react to this message? Create an account in a few clicks or log in to continue.
Oracle DBA FAQ

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


    How to find the waiting object and row

    avatar
    Admin
    Admin


    Posts : 17
    Join date : 2010-02-08

    How to find the waiting object and row Empty How to find the waiting object and row

    Post  Admin 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 Fri Apr 26, 2024 8:42 am