Code Listing 6: Selecting from the Active Session History archive 

 

SQL> select session_id, sample_time, session_state, event,wait_time, time_waited, sql_id, sql_child_number CH#
  2  from dba_hist_active_sess_history
  3  where user_id = 92
  4  and sample_time between
  5      to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
  6         and
  7      to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
  8  order by 1,2;

                                     SESSION
SESSION_ID SAMPLE_TIME               _STATE   EVENT                           WAIT_TIME TIME_WAITED  SQL_ID        CH#
—————————— ————————————————————————— ———————  —————————————————————————————  —————————— ———————————  ————————————— ———
        39 29-SEP-12 04.55.11.389 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.55.21.399 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.55.31.419 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.55.41.429 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.55.51.439 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.56.01.449 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.56.11.459 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.56.21.469 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
        39 29-SEP-12 04.56.31.499 PM WAITING  enq: TX - row lock contention           0           0  fx60htyzmz6wv   0
… output truncated …