Kill inactive session in oracle database using shell script automatically
Step 1: Find out the 30 minutes old inactive sessions using below SQL query.
In my case i am creating a script with the name "inactive_session_more_than_30_minutes.sql". You can create the script with any desired name.
Location of my script: /u01/dba/scripts/inactive_session_more_than_30_minutes.sql
For Oracle RAC Database
------------------------
$vi /u01/dba/scripts/inactive_session_more_than_30_minutes.sql
set lines 600
set pages 600
TTITLE OFF
SET HEAD OFF
SELECT 'alter system kill session ''' || sid || ','|| serial# || ','||'@'||inst_id||''' immediate;' FROM GV$SESSION S
WHERE S.STATUS = 'INACTIVE'
S.type!= 'BACKGROUND'
AND S.TYPE='USER'
AND WAIT_CLASS = 'Idle'
AND round(SECONDS_IN_WAIT/60,2) > 30
AND S.last_call_et > 30;
For Oracle NON-RAC Database
----------------------------
$vi /u01/dba/scripts/inactive_session_more_than_30_minutes.sql
set lines 600
set pages 600
TTITLE OFF
SET HEAD OFF
SELECT 'alter system kill session ‘||’ ‘||””||s.sid||’,’||s.serial# ||”’ immediate;’ FROM V$SESSION S
WHERE S.STATUS = 'INACTIVE'
S.type!= 'BACKGROUND'
AND S.TYPE='USER'
AND WAIT_CLASS = 'Idle'
AND round(SECONDS_IN_WAIT/60,2) > 30
AND S.last_call_et > 30;
Step 2: Create shell script using above script
In shell scripting we are calling the '/u01/dba/scripts/inactive_session_more_than_30_minutes.sql' script which used to find out the 30 minutes old inactive sessions and create new script using the output of '/u01/dba/scripts/inactive_session_more_than_30_minutes.sql' step 1 query.
$vi /u01/dba/scripts/kill_inactive_30_minutes_sessions.sh
export ORACLE_SID=ERPDBA
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1/
$ORACLE_HOME/bin/sqlplus -S "/as sysdba" @u01/dba/scripts/inactive_session_more_than_30_minutes.sql > /u01/dba/scripts/kill_inactive_30_minutes_sessions.sh
$ORACLE_HOME/bin/sqlplus -S "/as sysdba" @/u01/dba/scripts/kill_inactive_30_minutes_sessions.sh > /u01/dba/scripts/kill_inactive_30_minutes_sessions.log
exit
Change the permission of shell script
$ chmod 775 kill_inactive_ses.sh
Step 3: Schedule shell script in crontab
This is final step, in this step we are scheduling the shell script in crontab
$crontab -e
*/5 * * * * /u01/dba/scripts/kill_inactive_30_minutes_sessions.sh