SQL Script Session Idle Time

Updated: 24-Nov-2004
Version: 2,0

Description

Two different ways to monitor idle sessions. This script displays the top 5 longest idle times

Parameters

None.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


col sid form 999
col serial# form 99999
col spid form a6
col program form a25
col username form a10
col osuser form a10
col idle form a10 heading "Idle"
col terminal form a12
col logon_time form a18

spool idle.lst

PROMPT This query displays all those session who are set waiting at SQL prompts

select s.username,s.osuser,s.terminal, s.sid, s.serial#, p.spid, s.process , w.seq# 
from v$session s, v$process p ,v$session_wait w 
where p.addr = s.paddr and s.sid = w.sid 
and w.event = 'SQL*Net message from client' 
and s.status = 'INACTIVE' order by s.osuser, s.terminal
/

PROMPT This query lists how long those session have been idle for.

select rownum as rank, a.*
from (
  select sid,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
    , program 
  from v$session 
  where type='USER' order by last_call_et) a
where rownum < 6
/


spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links