Determine connections that have been idle for more than a certain amount of time
(be aware that last_call_et also shows number of seconds active also)


Examples

-- Connections idle for > 60 minutes 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60) >60
order by username, last_call_et desc;


-- Connections idle for > 1 day 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 1
order by username, last_call_et desc;

-- Connections idle for > 5 days 

select s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 5
order by username, last_call_et desc;