vusersid

rem  vusersid.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'

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

  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