Sunday 20 July 2014

Automatically kill database session inactive since 60 minutes

Step1: Create a shell script and put into a specific location

=========Shell script========

set serveroutput on size 100000
set echo off
set feedback off
set lines 300
spool /back/exp/kill_session_info.sql
declare
 cursor sessinfo is select * from v$session where status = 'INACTIVE' and last_call_et>3600 ;
 sess sessinfo%rowtype;
 sql_string1 Varchar2(2000); 
 sql_string2 Varchar2(2000);
begin
dbms_output.put_line('SPOOL /back/exp/killed_session_info.log;');
 open sessinfo;
 loop
  fetch sessinfo into sess;
  exit when sessinfo%notfound;
  sql_string1:='–sid='||sess.sid||' serail#='||sess.serial#||' machine='||sess.machine||' program='||sess.program||' username='||sess.username||' Inactive_sec='||sess.last_call_et||' OS_USER='||sess.osuser;
  dbms_output.put_line(sql_string1);
  sql_string2:='alter system kill session '||chr(39)||sess.sid||','||sess.serial#||chr(39)||'  ;';
  dbms_output.put_line(sql_string2);
 end loop;
 close sessinfo;
 dbms_output.put_line('SPOOL OFF;');
 dbms_output.put_line('exit;');
end;

spool off;
set echo on;
set feedback on;
@/back/exp/kill_session_info.sql;

Step2: Create a cron job and reference the script

No comments:

Post a Comment