Query to Check Inactive Sessions

 
What to do when processes reach the maximum?

You can cleanup sessions that have been inactive for a long time or increase the processes parameter.

set lines 200
set pages 200
col alt_disc for a60
col spid for a10
col username for a20
col status for a10
col machine for a30
select 'alter system disconnect session ''' || sess.sid || ',' || sess.serial# ||''' immediate;' as alt_disc,
proc.spid,
sess.username,
sess.status,
sess.machine,
sess.last_call_et/60/60 as last_active_in_hours,
sess.logon_time
from v$session sess, v$process proc
where sess.username not in ('SYS','SYSTEM','DBSNMP')
and proc.addr (+) = sess.paddr
and sess.status = 'INACTIVE'
order by sess.last_call_et desc, trunc(sess.logon_time);

OR

set lines 200
set pages 200
col alt_disc for a60
col spid for a10
col username for a20
col status for a10
col machine for a30
select 'alter system disconnect session ''' || sess.sid || ',' || sess.serial# ||''' immediate;' as alt_disc
from v$session sess, v$process proc
where sess.username not in ('SYS','SYSTEM','DBSNMP')
and sess.status = 'INACTIVE'
and proc.addr (+) = sess.paddr
order by sess.last_call_et desc, trunc(sess.logon_time);

OR

select username,
status,
count(*)
from v$session
group by username, status
order by 3 desc;

Use the query below to check for the process ids of sessions that have been killed.

select 'kill -9 ' || spid
from v$process
where addr not in (select paddr from v$session) and program like '%TNS%';

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart