QUERY TO FIND USERS IDLE SESSIONS IN ORACLE 11G RAC

• SCRIPT FOR IDENTIFIYING IDLE SESSIONS SINCE 1HR IN RAC

set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,inst_id,
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",
program
from
gv$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

• SCRIPT FOR IDENTIFIYING IDLE SESSIONS SINCE 2HR IN RAC

set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,serial#,username,status,inst_id,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/7200)||':'||
floor(mod(last_call_et,7200)/120)||':'||
mod(mod(last_call_et,7200),120) "IDLE",
program
from
gv$session
where
type='USER'
and (LAST_CALL_ET / 120) > &minutes
order by last_call_et;

• To display all the INACTIVE sessions of a particular user idle since 2 hrs

SQL> SELECT 'alter system kill session '||''''||sid ||','|| serial#||''''||' immediate;' FROM gv$session WHERE status ='INACTIVE'
     and last_call_et > 7200 and username='FMIS';




Happy Learning ....