[code language=”sql”]
— This will find the user id
select user_id
from dba_users
where username = ‘USERNAME’;
— here is an example I use
select username,
user_id
from dba_users
where username not like ‘NB%’ and
username not like ‘ZK%’;
— Change the user_id to whatever user ID you found before
col event format a30
col sample_time format a25
select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from v\$active_session_history
where user_id = 92
and sample_time between
to_date(’29-SEP-12 04.55.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and
to_date(’29-SEP-12 05.05.00 PM’,’dd-MON-yy hh:mi:ss PM’)
order by session_id, sample_time;
select SQL_TEXT
from v\$sql
where sql_id = ‘<sql_id>’;
select sample_time, session_state, blocking_session, current_obj#, current_file#, current_block#, current_row#
from v\$active_session_history
where user_id = <user id>
and sample_time between
to_date(’29-SEP-12 04.55.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and
to_date(’29-SEP-12 05.05.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and session_id = 39
and event = ‘enq: TX – row lock contention’
order by sample_time;
select
owner||’.’||object_name||’:’||nvl(subobject_name,’-‘) obj_name,
dbms_rowid.rowid_create (
1,
o.data_object_id,
row_wait_file#,
row_wait_block#,
row_wait_row#
) row_id
from v\$session s, dba_objects o
where sid = &sid
and o.data_object_id = s.row_wait_obj#
select sample_time, session_state, event, consumer_group_id
from v$active_session_history
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 session_id = 44
order by 1;
select sample_time, session_state, event, consumer_group_id
from v$active_session_history
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 session_id = 44
order by 1;
Checking all events from a machine
select event, count(1)
from v$active_session_history
where machine = ‘prolaps01′
and sample_time between
to_date(’29-SEP-12 04.55.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and
to_date(’29-SEP-12 05.05.00 PM’,’dd-MON-yy hh:mi:ss PM’)
group by event
order by event;
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;
[/code]