-- 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;
set termout off; set feedback off set echo off; column client format a45; column username format a14; column name format a14 column tablespace_name format a50 column db format a9 column db_host format a14 set linesize 100 set pagesize 300 select d.name DB, i.host_name db_host, s.username, s.machine client, count(*) tot from v\$session s, v\$database d, v\$instance i where s.username != 'SYS' group by s.machine, s.username, d.name, i.host_name, i.status order by tot, s.username desc; exit; ############################################################################################### #!/bin/ksh # Session trace ############################################################################################### echo echo "Session IDs and what's being executed " echo "======================================" sqlplus -s "/ as sysdba" <<EOF set pages 66 set lines 150 column username format a15 word_wrapped column module format a25 word_wrapped column action format a20 word_wrapped column client_info format a30 word_wrapped column service_name format a30 word_wrapped column client_identifier format a30 word_wrapped column sql_id format a15 word_wrapped column event format a50 word_wrapped column seconds_in_wait format 999,999 select username||'('||sid||','||serial#||')' username, module, action, client_info, service_name, client_identifier, sql_id, event, seconds_in_wait from v\$session /* where module||action||client_info is not null; */ where status='ACTIVE' and username is not null and username like 'SYS%' and username not like 'FOG%'; SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv\$session s JOIN gv\$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND'; EOF
SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND';