-- 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;
/************************************************************************** * Description: * SQL*Plus script to start the SQL Trace for another session. This * script displays the trace file name and location. This script will * display the user sessions and prompt for information it needs. * **************************************************************************/ SET TERMOUT OFF SET FEEDBACK OFF SET VERIFY OFF SET ECHO OFF column in_db_namet new_value out_db_namet noprint column in_spidt new_value out_spidt noprint column in_usert new_value out_usert noprint column in_patht new_value out_patht noprint select value in_db_namet, user in_usert from v\$parameter where name = 'db_name'; -- Find the path of the trace file directory select value in_patht from v\$parameter where name = 'user_dump_dest'; -- This will put identifiable characters into the trace file and make it easier to find alter session set tracefile_identifier = &out_usert; SET TERMOUT ON column program format a20 select username, sid, serial#, program from v\$session where program NOT LIKE 'ORACLE%'; ACCEPT in_user PROMPT 'Enter User ID from list above : ' ACCEPT in_sid PROMPT 'Enter SID from list above : ' ACCEPT in_serial PROMPT 'Enter SERIAL from list above : ' PROMPT PROMPT 'Write the SID and SERIAL down...will need them to stop the trace' PROMPT SET TERMOUT OFF select p.spid in_spidt from v\$process p, v\$session s where p.addr = s.paddr and s.username = upper('&in_user') and s.sid = &in_sid; SET TERMOUT ON PROMPT PROMPT Started SQL Trace in session SELECT '&out_path' "Trace File Path" from dual; SELECT '&out_db_namet' || '_ora_' || '&out_spidt' || '.trc' "Trace File Name" from dual; PROMPT rem exec dbms_system.set_ev(&in_sid, &in_serial, 10046, 12, ''); EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &in_sid, serial_num => &in_serial, waits => TRUE, binds => TRUE); SET FEEDBACK ON SET VERIFY ON SET ECHO ON This will start a trace in another session exec dbms_system.set_sql_trace_in_session(3,5023,true); exec dbms_system.set_sql_trace_in_session(3,5023,false);