Script: Display the sessions distribution and top 50 idle sessions

by

This script displays the top 50 longest idle times and list distribution of sessions on the instance

REM Copyright (C) Think anbob.com  2013-2015. All rights reserved. 

col sid form 99999
col serial# form 99999
col spid form a6
col program form a25
col username form a10
col osuser form a10
col idle form a30 heading "Idle"
col terminal form a12
col logon_time form a18
col machine for a20
col rn for 9999


break  on report
compute sum of cnt on report
select username,status,count(*) cnt from v$session group by username,status
/

select username,machine,count(*) cnt from v$session group by username,machine
/

ttitle - 
   center  'displays the top 50 longest idle times'  skip 2 

select  a.*
from (
  select sid,serial#,username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') logon_time
    , floor(last_call_et/3600)||' hours '
        || floor(mod(last_call_et,3600)/60)||' mins '
        || mod(mod(last_call_et,3600),60)||' secs' idle
    , machine ,row_number() over(order by last_call_et desc ) rn
  from v$session 
  where type='USER' ) a
where rn<= 50
/

ttitle off