AUTOMATICALLY KILL INACTIVE SESSIONS

Hi, today we are going to create script which automatically kill inactive sessions in oracle database. Here i’m going to kill 15 minutes old inactive sessions.

Related Keywords: oracle inactive sessions not clearing, kill inactive session in oracle more than 60 minutes,
shell script to kill inactive sessions in oracle, jdbc thin client inactive sessions, script to kill inactive sessions in oracle rac, impact of inactive sessions in oracle, oracle table lock kill session, oracle kill session.

Step 1: Find 15 minutes old inactive sessions

To find the 15 miuntes old inactive sessions we are using the below command. Copy the below command and create a script with “.sql” extension. In my case i am creating a script with the name “inactive_ses.sql“. You can create the script with any name.

Location of my script: /u01/scripts/inactive_ses.sql

$vi /u01/scripts/inactive_ses.sql

set lines 600
set pages 600
TTITLE OFF
SET HEAD OFF

SELECT 'Alter system kill session ''' || SID || ',' || SERIAL# || '''immediate;' FROM V$SESSION
WHERE STATUS != 'ACTIVE'
AND USERNAME = 'SCOTT'
AND WAIT_CLASS = 'Idle'
AND round(SECONDS_IN_WAIT/60,2) > 15;

Step 2: Create shell script using above script

In shell scripting we call the step 1 script which help us to find the 15 minutes old sessions and create new script using the output of step 1 query.

$vi /u01/scripts/kill_inactive_ses.sh

export ORACLE_SID=digital
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/scripts/inactive_ses.sql > /u01/scripts/kill_inactive_ses.sql
$ORACLE_HOME/bin/sqlplus -S "/as sysdba" @/u01/scripts/kill_inactive_ses.sql > /u01/scripts/kill_inactive_ses_out.log
exit

Change the permission of shell script

After creating the shell script we need to change the permission.

Before Permissions:

$ chmod 775 kill_inactive_ses.sh

After permissions:

Step 3: Schedule shell script in crontab

This is final step, in this step we schedule the shell script in crontab as you can see in below example.

$crontab -e

*/5 * * * * /u01/scripts/kill_inactive_ses.sh

Now the script has been completed.

I hope you learn somthing from this article, if yes please share your fillings with us on our social media.