CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
job_no number;
num_of_kills number := 0;
BEGIN
FOR REC IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND S.LAST_CALL_ET >= 4*60*60
AND S.status='INACTIVE'
ORDER BY INST_ID ASC
) LOOP
---------------------------------------------------------------------------
-- kill inactive sessions immediately
---------------------------------------------------------------------------
DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
execute immediate 'alter system disconnect session ''' || rec.sid || ', ' || rec.serial# || '''immediate' ;
DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
num_of_kills := num_of_kills + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of killed system sessions: ' || num_of_kills);
END DB_KILL_IDLE_CLIENTS;
$ vi /home/oracle/kill_idle_session.sh
. $HOME/.profile logfile=/home/oracle/killSession.log echo " " >> $logfile 2>&1 echo "START ----`date`" >> $logfile 2>&1 sqlplus /nolog <<STATS connect / as sysdba exec sys.db_kill_idle_clients; exit; STATS echo "END ------`date`" >> $logfile 2>&1
crontab -l
42 * * * * su - oracle -c /home/oracle/kill_idle_session.sh >>/home/oracle/kill_idle_session.log
