Wednesday, August 10, 2011

How To Identify Database Idle Sessions

The below scripts will identify the Database Idle Session .When on firing the below the scripts, it will prompt for the number of minutes the session is idle for.
SQL> select 
sid, username, status,
 to_char(logon_time,’dd-mm-yy hh:mi:ss’) “LOGON”,
floor(last_call_et/3600)||’:'||  floor(mod(last_call_et,3600)/60)||’:'||
mod(mod(last_call_et,3600),60) “IDLE”,
from   v$session
where  type=’USER’
and   (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

Enjoy    :-) 

No comments: