Monday, June 6, 2011

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
//

No comments:

Post a Comment