In the previous post, I've shared a script reports long running sessions/queries. In this post I'll explain another simple script that kills long running sessions connecting from specific programs e.g. SQL Developer
The script can be tuned to target any specific programs, but I'm mainly defaulting the script to target SQL Developer, as it's widely used by the developers, moreover, it doesn't maintain disconnecting the running session properly when the user close the session's window, keeping the "supposed to be closed" session query running in the database!
Download link:
https://www.dropbox.com/s/ke01v10bu0bat84/kill_long_running_queries.sh?dl=0
How to use:
The script is designed to be scheduled in the crontab to run every let's say 5 minutes to kill specific sessions based on the killing criteria which you can identify by editing the following parameters:
export ORACLE_SID=orcl
You have to provide the name of the instance you want to run the script against e.g. orcl
MAIL_LIST="youremail@yourcompany.com"
Provide your email to get a detailed report after each successful session kill.
export MODULE_NAME="'SQL Developer'"
You have to provide the module name that appears in v$session which you are targeting its sessions, in order to provide more than one module you have to enclose each module name between single quote putting comma "," in between.
e.g. To include SQL Developer and Toad sessions in the killing criteria you have to provide the values to MODULE_NAME parameter like the following:
export MODULE_NAME="'SQL Developer','Toad'"
To get an idea of module names that are connecting to your database run this statement:
select distinct(module_name) from gv$active_session_history;
export DURATION="2.5"
Duration parameter is the session's elapsed active time in hours which if reached the session will be a candidate for kill. It's set to 2 hours and half as a default value. of course you have to change it to whatever fits your requirements.
export REPORT_ONLY="N"
This parameter if set to "N" to enable the real kill of the candidate session, if it's set to "Y" it will not do the actual kill but will ONLY report the candidate session to your email. This parameter is helpful during testing phase, you've to test this script first by setting this parameter to "Y", it will keep reporting to you the candidate sessions (to be killed) without killing them, once you're confident with the script accuracy, set it to"N" to start killing the candidate sessions.
Ultimately, although I'm not keen to share such scripts that may cause problems if they are not accurately configured , I'm sharing this script in the hope that you will use it wisely, and in order to do so, you have to test and test and test such scripts before you deploy them on production.
By your use of this script you acknowledge that you use it at your own risk.
Github version:
The script can be tuned to target any specific programs, but I'm mainly defaulting the script to target SQL Developer, as it's widely used by the developers, moreover, it doesn't maintain disconnecting the running session properly when the user close the session's window, keeping the "supposed to be closed" session query running in the database!
Download link:
https://www.dropbox.com/s/ke01v10bu0bat84/kill_long_running_queries.sh?dl=0
How to use:
The script is designed to be scheduled in the crontab to run every let's say 5 minutes to kill specific sessions based on the killing criteria which you can identify by editing the following parameters:
export ORACLE_SID=orcl
You have to provide the name of the instance you want to run the script against e.g. orcl
MAIL_LIST="youremail@yourcompany.com"
Provide your email to get a detailed report after each successful session kill.
export MODULE_NAME="'SQL Developer'"
You have to provide the module name that appears in v$session which you are targeting its sessions, in order to provide more than one module you have to enclose each module name between single quote putting comma "," in between.
e.g. To include SQL Developer and Toad sessions in the killing criteria you have to provide the values to MODULE_NAME parameter like the following:
export MODULE_NAME="'SQL Developer','Toad'"
To get an idea of module names that are connecting to your database run this statement:
select distinct(module_name) from gv$active_session_history;
export DURATION="2.5"
Duration parameter is the session's elapsed active time in hours which if reached the session will be a candidate for kill. It's set to 2 hours and half as a default value. of course you have to change it to whatever fits your requirements.
export REPORT_ONLY="N"
This parameter if set to "N" to enable the real kill of the candidate session, if it's set to "Y" it will not do the actual kill but will ONLY report the candidate session to your email. This parameter is helpful during testing phase, you've to test this script first by setting this parameter to "Y", it will keep reporting to you the candidate sessions (to be killed) without killing them, once you're confident with the script accuracy, set it to"N" to start killing the candidate sessions.
Ultimately, although I'm not keen to share such scripts that may cause problems if they are not accurately configured , I'm sharing this script in the hope that you will use it wisely, and in order to do so, you have to test and test and test such scripts before you deploy them on production.
By your use of this script you acknowledge that you use it at your own risk.
Github version:
