Script For Killing Long Running Queries Running From Specific Programs By Specific Users | Updated Version

 On Apr 21, 2018 I've published a shell script to kill long-running sessions in Oracle DB connecting from specific MODULES and running longer than x number of minutes. I've revisited the script, adding more handy features, and make it compatible with 12c+ versions.

Here is a sum of all features: 

Under the following section, you can tweak the script settings and define the killing criteria :

Detailed description of script parameters you can tweak:

 

Here is the GitHub version of the script: [You can copy the content below]

https://gist.github.com/0aa26dee2de1005715d3d8ac01dddee0.git

	# ################################################################################################################
	# 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".

view raw kill_long_running_queries.sh hosted with ❤ by GitHub