ANBOB™

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

打赏

微信扫一扫，打赏作者吧～