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
exitChange 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.shNow 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.
