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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ################################################################################################################ | |
| # Kill QUERIES/SESSIONS running for more than N minutes based on specific criteria. | |
| # ################################################################################################################ | |
| VER="[2.5]" | |
| # # # # | |
| # Author: Mahmmoud ADEL # # # # ### | |
| # Created: 11-01-18 # # # # # | |
| # | |
| # Modified: 17-08-22 Included the following filters: | |
| # INCLUDE_USERS & EXCLUDE_USERS They are MUTUALLY EXCLUSIVE, only one to be set at a time. | |
| # 25-08-22 Added TEST_MODE option, to help the user test the script without killing any sessions. | |
| # 25-08-22 Set DURATION variable to minutes instead of hours. | |
| # 30-08-22 Added KILL_QUERY_ONLY option if set to Y will kill only the query instead of the session. | |
| # 30-08-22 Added SESSION_STATUS to allow the user to kill INACTIVE sessions. | |
| # be cautious when killing INACTIVE session if the application is using connection pooling. | |
| # 12-09-22 Added the option of excluding SQLIDs through EXCLUDESQLID parameter. | |
| # ################################################################################################################ | |
| # ########## #################################### | |
| # VARIABLES: [Here you TWEAK the script Settings] | |
| # ########## #################################### | |
| MAIL_KILL_LIST="youremail@yourcompany.com" # Email Recipients: To be notified when a session kill occurs. | |
| MAIL_REPORT_LIST=${MAIL_KILL_LIST} # Email Recipients: To be notified when TESTING mode is enabled. TEST_MODE="Y" | |
| ORACLE_SID= # Define the INSTANCE NAME the script will run against e.g. ORACLE_SID="ORCL" | |
| ##### INCLUDE_USERS and EXCLUDE_USERS are MUTUALY EXCLUSIVE; define only one of them at a time, if both defined; the script will consider only INCLUDE_USERS. | |
| INCLUDE_USERS="'TEST1'" # DB USERNAMES TO BE KILLED. e.g. INCLUDE_USERS="'TEST'" OR INCLUDE_USERS="'TEST','ELDAMARANI'" | |
| EXCLUDE_USERS="'SYS'" # DB USERNAMES TO NOT BE KILLED. e.g. EXCLUDE_USERS="'SYS'" OR EXCLUDE_USERS="'SYS','APPUSER'" | |
| MODULE="'SQL Developer','Toad'" # Candidate Modules to be KILLED. e.g. MODULE="'Toad'", or: MODULE="'SQL Developer','Toad'" | |
| DURATION="60" # Duration: [In Minutes]. e.g. to kill defined sessions running for more than 30minutes: DURATION="30" | |
| KILL_QUERY_ONLY=Y # KILL ONLY THE QUERY NOT THE SESSION [Works on 12c and above] | |
| TEST_MODE="Y" # This is TEST MODE. e.g. to report the long running sessions WITHOUT kill: TEST_MODE="Y" , to report & KILL TEST_MODE="N" | |
| SESSION_STATUS="ACTIVE" # Define the user's session status either ACTIVE or INACTIVE [Default is ACTIVE] | |
| EXCLUDESQLID="'ckajr8v22554b'" # EXCLUDED SQL_IDs. e.g. EXCLUDESQLID="'ckajr8v22554b','59p1yadp2g6mb'" | |
| SCRIPT_NAME="kill_long_queries.sh" # SCRIPT NAME | |
| SRV_NAME=`uname -n` # MACHINE NAME | |
| LOGDIR=/tmp # LOG DIRECTORY | |
| LOGFILE=${LOGDIR}/kill_long_queries.log # LOGFILE PATH | |
| TERMINATOR_SCRIPT=${LOGDIR}/KILL_LONG_QUERIES.sql # SQLPlus KILL SCRIPT PATH | |
| #export smtp="mailrelay.mycompany.com:25" # Set it to the SMTP NAME:PORT your company is using. [If exist] | |
| # ################## | |
| # Checking Variables: [Do NOT modify the lines below] | |
| # ################## | |
| # Check if the user provided a valid Email address: | |
| case ${EMAIL} in "youremail@yourcompany.com") | |
| echo | |
| echo "******************************************************************" | |
| echo "Buddy! You forgot to provide your EMail to MAIL_KILL_LIST variable." | |
| echo "Please replace youremail@yourcompany.com with your E-mail address," | |
| echo "to be able to receive a Notification when a session kill happens." | |
| echo "******************************************************************" | |
| echo | |
| esac | |
| # Check INCLUDE_USERS & EXCLUDE_USERS parameters and make sure to not collide: | |
| # If BOTH INCLUDE_USERS & EXCLUDE_USERS variables are DEFINED, then notify the user: | |
| if ! [ -z ${INCLUDE_USERS} ] && ! [ -z ${EXCLUDE_USERS} ] | |
| then | |
| echo "" | |
| echo "WARNING: You have defined both variables INCLUDE_USERS & EXCLUDE_USERS." | |
| echo "Note: The script will ONLY consider INCLUDE_USERS=${INCLUDE_USERS} and will ignore EXCLUDE_USERS list." | |
| echo | |
| export EXCLUDE_USERS="" | |
| export DBUSERNAMES=${INCLUDE_USERS} | |
| export PREDICATE="IN" | |
| fi | |
| # If INCLUDE_USERS is already set then UN-DEFINE EXCLUDE_USERS and set SQLPlus script variables accordingly: | |
| if ! [ -z ${INCLUDE_USERS} ] | |
| then | |
| echo "Note: INCLUDE_USERS variable is set." | |
| echo | |
| export EXCLUDE_USERS="" | |
| export DBUSERNAMES=${INCLUDE_USERS} | |
| export PREDICATE="IN" | |
| fi | |
| # If EXCLUDE_USERS is set then set SQLPlus script variables accordingly: | |
| if ! [ -z ${EXCLUDE_USERS} ] | |
| then | |
| echo | |
| echo "Note: EXCLUDE_USERS variable is set." | |
| echo | |
| export DBUSERNAMES=${EXCLUDE_USERS} | |
| export PREDICATE="NOT IN" | |
| fi | |
| # If BOTH INCLUDE_USERS & EXCLUDE_USERS variables are not set then hash the entire USERNAME filter line in SQLPlus script: [kill all sessions connecting through defined modules] | |
| if [ -z ${INCLUDE_USERS} ] && [ -z ${EXCLUDE_USERS} ] | |
| then | |
| echo | |
| echo "Note: USERNAME criteria is not been set." | |
| echo | |
| export HASHUSERNAME="--" | |
| fi | |
| # IF EXCLUDESQLID variable is empty: | |
| if [ -z ${EXCLUDESQLID} ] | |
| then | |
| export HASHSQLID="--" | |
| export SQLIDMSG="" | |
| else | |
| export HASHSQLID="" | |
| export SQLIDMSG="> SQLID NOT IN: ${EXCLUDESQLID}" | |
| fi | |
| # Check TEST_MODE Flag: | |
| case ${TEST_MODE} in | |
| Y|y|yes|Yes|YES) export HASH_SCRIPT="--";export TEST_MODE_MESSAGE="PROMPT TEST_MODE value is set to Y, No Kill will happen" | |
| echo | |
| echo -e "\033[33;5mTEST MODE is Activated, NO KILL will happen.\033[0m" | |
| echo | |
| export MAIL_LIST="${MAIL_REPORT_LIST}" | |
| export REPORT_ONLY_MSG="CANDIDATE TO BE" | |
| ;; | |
| *) export HASH_SCRIPT="";export TEST_MODE_MESSAGE="" | |
| export MAIL_LIST="${MAIL_KILL_LIST}" | |
| ;; | |
| esac | |
| # ####################################### | |
| # Excluded INSTANCES: | |
| # ####################################### | |
| # Here you can mention the instances dbalarm will IGNORE and will NOT run against: | |
| # Use pipe "|" as a separator between each instance name. | |
| # e.g. Excluding: -MGMTDB, ASM instances: | |
| EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline]. | |
| # ####################################### | |
| # Exporting Variables for child sessions: | |
| # ####################################### | |
| export MAIL_KILL_LIST | |
| export MAIL_REPORT_LIST | |
| export MODULE | |
| export DBUSERNAMES | |
| export EXCLUDE_USERS | |
| export DURATION | |
| export TEST_MODE | |
| export SESSION_STATUS | |
| export ORACLE_SID | |
| export SCRIPT_NAME | |
| export SRV_NAME | |
| export LNXVER | |
| export LOGFILE | |
| export TERMINATOR_SCRIPT | |
| export EXL_DB | |
| export HASH_KILL_QUERY="--" | |
| export HASH_KILL_SESSION="" | |
| export OBJECT="SESSIONS" | |
| # ######################### | |
| # Setting ORACLE_SID: | |
| # ######################### | |
| # Check if the user has set the ORACLE_SID: | |
| if [ -z ${ORACLE_SID} ] | |
| then | |
| echo "WARNING: You didn't set the ORACLE_SID inside the script!" | |
| echo "******* In order to accurately run this script you have to set the ORACLE_SID," | |
| echo " Otherwise the script will run against the only running DB if applicable." | |
| echo "" | |
| fi | |
| # If no Databases are up show a warning message: | |
| INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l ) | |
| if [ ${INS_COUNT} -eq 0 ] | |
| then | |
| ps -ef|grep -v grep|grep pmon | |
| echo "WARNING: No Databases Are Running on the Server!" | |
| echo | |
| ps -ef|grep -v grep|grep pmon | |
| echo "Script Terminated!" | |
| exit | |
| fi | |
| # Set ORACLE_SID to the only running Instance: [If the user didn't define a specific ORACLE_SID] | |
| if [ -z ${ORACLE_SID} ] && [ ${INS_COUNT} -eq 1 ] | |
| then | |
| export ORACLE_SID=`ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g"` | |
| fi | |
| if [ ${INS_COUNT} -gt 1 ] && [ -z ${ORACLE_SID} ] | |
| then | |
| echo "Error: Too many instances running!" | |
| echo "***** Please set the ORACLE_SID inside the script to accurately define the DB the script will run against." | |
| echo | |
| echo "Script Terminatd!" | |
| echo "" | |
| exit | |
| fi | |
| # ######################### | |
| # Getting ORACLE_HOME | |
| # ######################### | |
| ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1` | |
| USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1` | |
| # SETTING ORATAB: | |
| if [ -f /etc/oratab ] | |
| then | |
| ORATAB=/etc/oratab | |
| export ORATAB | |
| ## If OS is Solaris: | |
| elif [ -f /var/opt/oracle/oratab ] | |
| then | |
| ORATAB=/var/opt/oracle/oratab | |
| export ORATAB | |
| fi | |
| # ATTEMPT1: Get ORACLE_HOME using pwdx command: | |
| PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'` | |
| export PMON_PID | |
| ORACLE_HOME=`pwdx ${PMON_PID} 2>/dev/null|awk '{print $NF}'|sed -e 's/\/dbs//g'` | |
| export ORACLE_HOME | |
| # ATTEMPT2: If ORACLE_HOME not found get it from oratab file: | |
| if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] | |
| then | |
| ## If OS is Linux: | |
| if [ -f /etc/oratab ] | |
| then | |
| ORATAB=/etc/oratab | |
| ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'` | |
| export ORACLE_HOME | |
| ## If OS is Solaris: | |
| elif [ -f /var/opt/oracle/oratab ] | |
| then | |
| ORATAB=/var/opt/oracle/oratab | |
| ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'` | |
| export ORACLE_HOME | |
| fi | |
| fi | |
| # ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command: | |
| if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] | |
| then | |
| ORACLE_HOME=`dbhome "${ORACLE_SID}"` | |
| export ORACLE_HOME | |
| fi | |
| # ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate] | |
| if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] | |
| then | |
| ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'` | |
| export ORACLE_HOME | |
| fi | |
| # ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate] | |
| if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] | |
| then | |
| ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1` | |
| export ORACLE_HOME | |
| fi | |
| # ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate] | |
| if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] | |
| then | |
| if [ -x /usr/bin/locate ] | |
| then | |
| ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'` | |
| export ORACLE_HOME | |
| fi | |
| fi | |
| # ################### | |
| # Getting DB Version: | |
| # ################### | |
| #echo "Checking DB Version" | |
| DB_VER_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF | |
| set pages 0 feedback off lines 1000; | |
| prompt | |
| select version from v\$instance; | |
| exit; | |
| EOF | |
| ) | |
| export DB_VER=`echo ${DB_VER_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'` | |
| # Is it KILL SESSION or DISCONNECT SESSION in the alter system command: [If DB version is <11g use KILL if >=11g then use DISCONNECT keyword] | |
| if [ ${DB_VER} -gt 10 ] | |
| then | |
| export KILLARG="DISCONNECT" | |
| else | |
| export KILLARG="KILL" | |
| fi | |
| # If the KILL_QUERY_ONLY is set to Y and Database version is 12c and above disable the KILL SESSION command and enable the CANCEL QUERY command: | |
| if [ ${DB_VER} -gt 11 ] && [ ${KILL_QUERY_ONLY} = "Y" ] | |
| then | |
| echo "Note: KILL QUERY MODE IS ACTIVATED. [DB_VERSION=${DB_VER} and KILL_QUERY_ONLY=${KILL_QUERY_ONLY}]" | |
| echo | |
| export HASH_KILL_SESSION="--" | |
| export HASH_KILL_QUERY="" | |
| export OBJECT="QUERY" | |
| fi | |
| # ########### | |
| # SCRIPT BODY: | |
| # ########### | |
| # Flush the logfile: | |
| cat /dev/null > ${LOGFILE} | |
| # CHECKING RUNNING SESSIONS: | |
| SESSIONS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF | |
| set pages 0 feedback off; | |
| prompt | |
| select count(*) from GV\$SESSION where | |
| MODULE in (${MODULE}) | |
| ${HASHUSERNAME} and username ${PREDICATE} (${DBUSERNAMES}) | |
| ${HASHSQLID} and SQL_ID not in (${EXCLUDESQLID}) | |
| and last_call_et > 60*${DURATION} | |
| and status = '${SESSION_STATUS}' | |
| and wait_class <>'Idle' | |
| ; | |
| exit; | |
| EOF | |
| ) | |
| SESSIONS_COUNT=`echo ${SESSIONS_COUNT_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'` | |
| #echo SESSIONS_COUNT_RAW is: $SESSIONS_COUNT_RAW | |
| #echo SESSIONS_COUNT is: $SESSIONS_COUNT_RAW | |
| # KILLING LONG RUNNING SESSIONS IF EXIST: | |
| # ###################################### | |
| if [ ${SESSIONS_COUNT} -gt 0 ] | |
| then | |
| echo "Found ${SESSIONS_COUNT} Candidate ${OBJECT} to be killed!" | |
| KILL_SESSION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF | |
| spool ${LOGFILE} APPEND | |
| set pages 0 feedback off | |
| prompt | |
| PROMPT ***** | |
| select 'TIME: '||to_char(systimestamp, 'dd-Mon-yy HH24:MI:SS') from dual; | |
| PROMPT ***** | |
| set linesize 170 pages 1000; | |
| prompt | |
| prompt Session Details: [${OBJECT} To be killed] | |
| prompt *************** | |
| col inst for 99 | |
| col module for a27 | |
| col event for a28 | |
| col MACHINE for a27 | |
| col "ST|ACT_SINC|LOG_TIME" for a51 | |
| col "USER|OS|SID,SER|MACHIN|MODUL" for a75 | |
| select substr(USERNAME||'|'||OSUSER||'|'||sid||','||serial#||'|'||substr(MACHINE,1,25)||' | '||substr(MODULE,1,25),1,75)"USER|OS|SID,SER|MACHIN|MODUL" | |
| ,substr(status||'|'||LAST_CALL_ET||'|'||LOGON_TIME,1,50) "ST|ACT_SINC|LOG_TIME" | |
| ,SQL_ID CURR_SQL_ID | |
| from gv\$session | |
| where | |
| MODULE in (${MODULE}) | |
| ${HASHUSERNAME} and username ${PREDICATE} (${DBUSERNAMES}) | |
| and last_call_et > 60*${DURATION} | |
| and status = '${SESSION_STATUS}' | |
| ${HASHSQLID} and SQL_ID not in (${EXCLUDESQLID}) | |
| and wait_class <>'Idle' | |
| ; | |
| spool off | |
| -- Kill SQL Script creation: | |
| set pages 0 feedback off echo off | |
| spool ${TERMINATOR_SCRIPT} | |
| ${HASH_KILL_SESSION} select /*+RULE*/ 'ALTER SYSTEM ${KILLARG} SESSION '''||sid||','||serial#||',@'||inst_id||''' IMMEDIATE;' | |
| ${HASH_KILL_QUERY} select /*+RULE*/ 'ALTER SYSTEM CANCEL SQL '''||sid||','||serial#||',@'||inst_id||','||SQL_ID||''';' | |
| from GV\$SESSION | |
| where | |
| MODULE in (${MODULE}) | |
| ${HASHUSERNAME} and username ${PREDICATE} (${DBUSERNAMES}) | |
| and last_call_et > 60*${DURATION} | |
| and status = '${SESSION_STATUS}' | |
| ${HASHSQLID} and SQL_ID not in (${EXCLUDESQLID}) | |
| and wait_class <>'Idle' | |
| ; | |
| spool off | |
| -- Run the Terminator Script to kill the sessions: | |
| set pages 1000 feedback on echo on | |
| spool ${LOGFILE} APPEND | |
| PROMPT | |
| PROMPT Running The Terminator Script: | |
| PROMPT ***************************** | |
| ${TEST_MODE_MESSAGE} | |
| ${HASH_SCRIPT}START ${TERMINATOR_SCRIPT} | |
| spool off | |
| exit; | |
| EOF | |
| ) | |
| sleep 10 | |
| CURRENT_LONG_SESS_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF | |
| set linesize 170 pages 1000; | |
| spool ${LOGFILE} APPEND | |
| prompt | |
| prompt CHECK For other long queries fulfill the killing criteria: [Still Running] | |
| prompt ********************************************************* | |
| col inst for 99 | |
| col module for a27 | |
| col event for a28 | |
| col MACHINE for a27 | |
| col "ST|ACT_SINC|LOG_TIME" for a51 | |
| col "USER|SID,SER|MACHIN|MODUL" for a72 | |
| select substr(USERNAME||'|'||sid||','||serial#||'|'||substr(MACHINE,1,25)||' | '||substr(MODULE,1,25),1,72)"USER|SID,SER|MACHIN|MODUL" | |
| ,substr(status||'|'||LAST_CALL_ET||'|'||LOGON_TIME,1,50) "ST|ACT_SINC|LOG_TIME" | |
| ,SQL_ID CURR_SQL_ID | |
| from gv\$session | |
| where | |
| MODULE in (${MODULE}) | |
| ${HASHUSERNAME} and username ${PREDICATE} (${DBUSERNAMES}) | |
| and last_call_et > 60*${DURATION} | |
| and status = '${SESSION_STATUS}' | |
| ${HASHSQLID} and SQL_ID not in (${EXCLUDESQLID}) | |
| and wait_class <>'Idle' | |
| ; | |
| spool off | |
| exit; | |
| EOF | |
| ) | |
| # EMAIL Notification with the killed session: | |
| cat ${LOGFILE} | |
| echo | |
| echo "Sending Email Notification to: ${MAIL_LIST}" | |
| echo | |
| /bin/mail -s "Info: Long Running ${OBJECT} ${REPORT_ONLY_MSG} KILLED on [${ORACLE_SID}]" ${MAIL_LIST} < ${LOGFILE} | |
| else | |
| echo "" | |
| echo "On Instance: ${ORACLE_SID}" | |
| echo -e "\033[33;5m No Candidate ${OBJECT} to be KILLED are found as per the given Criteria:\033[0m" | |
| echo " > MODULE is: ${MODULE}" | |
| echo " > USERNAME ${PREDICATE} (${DBUSERNAMES})" | |
| echo " ${SQLIDMSG}" | |
| fi | |
| # ############# | |
| # END OF SCRIPT | |
| # ############# | |
| # REPORT BUGS to: mahmmoudadel@hotmail.com | |
| # DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM: | |
| # http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html | |
| # DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". |
