SQL | IT Remote


-- 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);

ITRemote

Ready for Action?