Title: Full User Details for Oracle Applications

Author: Raghu Eppalapalli, a Contract DBA in Irving, Texas.

This script lets the DBA/Unix administrator determine the sid,Pid,UserID, last hit to the database, the state (Active or inactive),
Logical I/O and Physical I/O and the Hit Ratio of the User order by User.

set pages 10
break on User_ID skip 1
spool user_details
rem
select substr(a.sid,1,6) sys_ID,
       substr(b.serial#,1,7) SERIAL#,
       substr(to_char(sysdate -(hsecs-a.value)/(24*3600*100),
       'MM/DD/YY:HH:MI:SS'),1,17) Last_Access_Time,
       substr(b.osuser,1,7) User_ID,
       substr(decode(b.osuser,'OraUser',d.spid,b.process),1,7) PROCESS,
       decode(b.status,'ACTIVE','....,','INACTIVE','Idle ') state,
       substr((e.consistent_Gets + e.block_Gets),1,7) Log_IO,
       substr(e.Physical_reads,1,7) Phy_IO,
       substr
        (round(100 * ((e.consistent_Gets + e.block_Gets - e.Physical_reads) /
             (e.consistent_Gets + e.block_Gets)),2),1,8) HitRatio
from v$sesstat a,
     v$session b,
     v$statname c,
     v$process d,
     v$sess_io e,
     v$timer
where  a.sid= b.sid
  and  a.statistic#=c.statistic#
  and  a.sid = e.sid
  and  a.value !=0
  and  b.sid = e.sid
  and  b.paddr = d.addr
  and  b.username is not null
  and  c.statistic# = '14'
  and  c.class = '128'
  and  (e.consistent_Gets + e.block_Gets) > 0
group by
         b.osuser,
         a.sid,
         b.serial#,
         substr(to_char(sysdate -(hsecs-a.value)/(24*3600*100),
         'MM/DD/YY:HH:MI:SS'),1,17),
         d.spid,
         b.process,
         b.status,
         e.consistent_Gets,
         e.block_Gets,
         e.Physical_reads
order by b.osuser
/
spool off
exit
/