Sunday, June 8, 2014

Getting row lock information from the Active Session History archive

select sample_time, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_ROWID.ROWID_create (
        1,
        o.data_object_id,
        current_file#,
        current_block#,
        current_row#
    ) row_id
from dba_hist_active_sess_history s, dba_objects o
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1,2;

No comments:

Post a Comment