rem vusercm.sql
rem
set linesize 132
rem
ttitle 'User Session by Concurrent Manager Request ID'
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 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
rem break on report
rem compute sum of log_per_sec phy_per_sec on report
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,
s.module program
from v$process p, v$session s, v$sess_io i,
applsys.fnd_concurrent_requests r
where i.sid = s.sid
and s.paddr = p.addr
and r.oracle_session_id = s.audsid
and r.request_id = &request;