Fight againts Oracle Idle Sessions II

What about if we are using ORACLE on windows? Today two scripts, very simple, to  kill sniped sessions on windows, using orakill tool.

Only need execute cleansessions.cmd.

— cleansessions.cmd
del kill.sql
del kill.bat
sqlplus “sys/****@[SID] as sysdba” @cleansessions.sql >> cleansessions.log
kill.bat
exit

— cleansessions.sql
select to_char(sysdate,’DD/MM/YYYY HH24:MI’) from dual;

select to_char(sum(maxmem),’9999G999G999′) totalPGA, estado, count(*) as NumSess
from
(select max(se.value) maxmem, s.status as Estado
from v$sesstat se, v$statname n, v$session s
where n.statistic# = se.statistic#
and n.name = ‘session pga memory’
and s.sid=se.sid
group by n.name,se.sid,s.osuser,s.serial#, s.status)
group by Estado
;

set heading off;
set feedback off;

spool kill.bat;

select ‘ORAKILL [SID] ‘ || p.spid || ‘ ‘ as comando
from v$process p, v$session s
where p.addr = s.paddr
and s.status=’SNIPED’
and s.logon_time<(SYSDATE-(1/24))
and s.last_call_et>3600;

spool off;

exit;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s