/* ************************************************ */
/*                                                  */
/* Format information about the browser users       */
/*                                                  */
/* name:    @c:\oracle\sql\brwusrck.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */
whenever sqlerror exit 

set pagesize 0
set verify off
set heading off
set feedback off

column username format A8
column osuser format A8
column process format A8
column sid format 99999
column systemdate format A14
column idle_time format  9999
column query_date format A14

select a.username username, 
       a.osuser osuser, 
       a.process process, 
       a.sid sid, 
       to_char(sysdate,'MM/DD HH24:MI:SS') systemdate, 
       min(to_char(c.query_date,'MM/DD HH24:MI:SS')) query_date,
       trunc((sysdate-min(query_date))*1440) idle_time
  from v$session a, v$sess_io b, sys.session_history c
 where a.sid = b.sid
   and a.username = c.username
   and a.osuser = c.osuser
   and a.process = c.process
   and a.sid = c.sid
   and b.block_gets = c.block_gets
   and b.consistent_gets = c.consistent_gets
   and b.physical_reads = c.physical_reads
   and b.block_changes = c.block_changes
   and b.consistent_changes = c.consistent_changes   
   and a.program like '%BROW%'
   and a.username like 'U%'
   and a.status = 'INACTIVE'
 group by a.username, a.osuser, a.process, a.sid, sysdate
 order by a.sid;
