haballan/kill_long_running_queries.sh

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