/* ckactmt.sql
     check server tiers active sessions
     note: state of WAITING means session is currently waiting
*/
col usersrvr format a12 trunc head ServerName 
col logonat format a12 head LoggedOn
col osuser format a8 trunc
col event head Current_Wait_Event format a25 trunc
col state format a7 trunc
col sessprog format a12 head SessProg trunc
col spid format a8 head DBProc
col LastCallET     format a11
col holdinglatch format a20 trunc head HoldingLatch
col sid format 99999
break on usersrvr skip 1 on report
compute count of sid on usersrvr
compute count of sid on report

select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid,
to_char(s.logon_time,'mm/dd hh24:mi') logonat,
   floor(last_call_et/3600)||':'||
   floor(mod(last_call_et,3600)/60)||':'||
   mod(mod(last_call_et,3600),60) "LastCallET",
s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid,
decode(h.sid,null,'None',h.name) holdinglatch
from v$session s, v$session_wait w, v$process p, v$latchholder h
where type = 'USER'
and status = 'ACTIVE'
and s.sid = w.sid 
and s.paddr = p.addr
and s.osuser is not null
and s.sid = h.sid (+)
order by 1,4
/