Inactive Sessions and Active Sessions in Oracle

Inactive Sessions

Inactive for more than 1 hour and kill those sessions.

The sql to be executed is given below

Sql> select 'alter system kill session '||''''||sid||','||serial#||''' immediate;',(sysdate-logon_time)/24

        from v$session

        where username is not null and status='INACTIVE' and (sysdate-logon_time)/24 > .08333333;

   Find Time :
2 hour
sql >  select 2/24 from dual;
o/p : .08333333

sql > select 'alter system kill session '''||sid||','||serial#||''' immediate;'

         from v$session

        where status='INACTIVE' and machine='your hostname.domainname';

Active Sessions

/* ckactmt.sql

     check server tiers active sessions

     note: state of WAITING means session is currently waiting

*/

col usersrvr format a12 trunc head ServerName

col logonat format a12 head LoggedOn

col osuser format a8 trunc

col event head Current_Wait_Event format a25 trunc

col state format a7 trunc

col sessprog format a12 head SessProg trunc

col spid format a8 head DBProc

col LastCallET     format a11

col holdinglatch format a20 trunc head HoldingLatch

col sid format 99999

break on usersrvr skip 1 on report

compute count of sid on usersrvr

compute count of sid on report

select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid,

to_char(s.logon_time,'mm/dd hh24:mi') logonat,

   floor(last_call_et/3600)||':'||

   floor(mod(last_call_et,3600)/60)||':'||

   mod(mod(last_call_et,3600),60) "LastCallET",

s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid,

decode(h.sid,null,'None',h.name) holdinglatch

from v$session s, v$session_wait w, v$process p, v$latchholder h

where type = 'USER'

and status = 'ACTIVE'

and s.sid = w.sid

and s.paddr = p.addr

and s.osuser is not null

and s.sid = h.sid (+)

order by 1,4

//