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