select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS, MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') from v$session where paddr in (select addr from v$process where spid = '&spid');
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid);
col sid format 999999 col username format a20 col osuser format a15 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid;
SELECT
nvl(utl_raw.cast_to_varchar2(s.paddr),'NO SESSION') No_Session,
p.inst_id,
p.spid,
p.pid,
s.sid,
p.pname,
s.serial#,
s.status,
s.username,
s.osuser,
s.program
FROM gv$process p, gv$session s
WHERE s.paddr(+) = p.addr
AND p.background is null
and p.pname not like ('D%')
and p.pname not like ('S%')
and p.pname not like ('P%')
and p.pname not like ('J%')
and p.pname not like ('W%')
ORDER BY No_Session, pname;
set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;
select * from dba_hist_active_sess_history where sql_id = '6qn5kcqgbzz0y' Session Trace select sid, serial#, status, username from v$session where username = 'MASHEGOG'; EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>393, serial#=> 25895, sql_trace=>TRUE); tkprof pint_ora_6492.trc pint_ora_6492.out explain=cai/sfqm6cfi sys=no
exec dbms_monitor.database_trace_enable(); exec dbms_monitor.database_trace_disable();
SQL> alter session set events '10046 trace name context forever, level 12'; SQL>SQL> alter session set events '10046 trace name context off';
tkprof rfilen_ora_22311.trc translated02.txt explain=a157584/sep082015 sys=no
#!/bin/sh
rm a1.lst
rm a2.lst
touch a1.lst
touch a2.lst
ls -l | awk '{print $9}' | grep "rfilen_ora_" | grep "trc" > a1.lst
for i in `cat a1.lst`
do
echo 'tkprof' `echo $i` `echo $i.txt explain=a157584/sep082015 sys=no` >> a2.lst
done
chmod 755 a2.lst
rm a1.lst
ls -l *.trc | awk '{ print "tkprof " $9 " explain=a157584/******* sys=no output=" $9 ".out" }' > a1.lst
Delete trc files for specified date
ls -l | grep "J" |grep "trm" | awk '{print "rm " $9}' > a.lst
ls -l | grep "2018" | grep “oracle “ | awk '{print "rm " $9}' > a1.lst
-- trcsess output=trace_batch.trc service=scd *.trc trcsess output=trace_batch.trc service=rfilen *.trc tkprof trace_batch.trc trace_batch_Sep08.out explain=a157584/sep082015 sys=no -- trcsess output=trace_batch.trc service=rfilen *.trc tkprof trace_batch.trc trace_batch.out explain=a157584/nov172017 sys=no exec DBMS_MONITOR.SESSION_TRACE_ENABLE(266, 49293);
This query returns the rollback status of the transaction
select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'))
This query returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done
select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk) from v$transaction t, v$session s where t.addr = s.taddr group by s.sid, s.serial#, s.client_info, t.addr;
Note: The client_info column may or may not be populated (it is not in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored.
The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.
Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyones life (at least for those who are waiting on a rollback to finish) a bit easier.
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' as script from gv$session where sid = 99999 and serial# = 99999;