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