rem vusersidsql.sql
rem
set linesize 132
rem
ttitle 'User Session Statistics'
rem
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a10 heading 'ORACLE|USERNAME'
col log_per_sec format 999999 heading 'LOG|PER|SEC'
col logical format b9999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a32 heading 'PROGRAM NAME'
col module format a32 heading 'MODULE'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 9999 heading 'LAST|CALL|MIN'
col status format a1 heading 'S'
col hide_sql_hash_value noprint new_value _sql_hash_value
rem
select s.process,
p.spid,
/*
p.pid,
*/
s.sid,
s.serial#,
s.osuser,
s.username,
( i.block_gets + i.consistent_gets ) /
( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
i.block_gets + i.consistent_gets logical,
physical_reads /
( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
i.physical_reads,
/*
s.audsid,
*/
to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
s.last_call_et/60 last_call_min,
decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,
decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module,
s.sql_hash_value hide_sql_hash_value
from v$process p, v$session s, v$sess_io i
where i.sid = s.sid
and s.paddr = p.addr
and s.sid = &sid;
rem
set linesize 80
rem
ttitle 'SQL Text'
rem
col sql_text format a64 heading 'SQL TEXT'
rem
select sql_text
from v$sqltext
where hash_value = &_sql_hash_value
and &_sql_hash_value <> 0
order by piece;