Showing posts with label Kill Database session automatically. Show all posts
Showing posts with label Kill Database session automatically. Show all posts

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