Script to Monitor Oracle Database Sessions Consuming High TEMP Tablespace

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check database sessions which are consuming TEMP space more than given threshold(in MB) of TEMP tablespace 
and to send alert notification with the database session details. 



Download(TempTsUsage.sh)

Download TempTsUsage.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 TempTsUsage.sh

#!/bin/bash

####################################################################################################
# Script Name : TempTsUsage.sh                                                                     #
#                                                                                                  #
# Description:                                                                                     #
# Script to check database sessions which are consuming TEMP space more than given threshold(in MB)#
# of TEMP tablespace and to send alert notification with the database session details              #          
#                                                                                                  #
# Usage : sh <script_name> <ORACLE_SID> <Used in MB>                                               #
# For example : sh TempTsUsage.sh ORCL 200                                                         #
#                                                                                                  #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                                        #
####################################################################################################

# Initialize variables

INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        #cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to check TEMP Tablespace usage for the database session"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Used in MB> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 200" >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!

 set feedback off
 set head off
 set feed off;
 set veri off;
 select count(1) from (
 SELECT   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
 S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
 COUNT(*) sort_ops
 FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
 WHERE    T.session_addr = S.saddr
 AND      S.paddr = P.addr
 AND      T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
 S.program, TBS.block_size, T.tablespace,S.MACHINE)
    where MB_USED > $THRESHOLD;

    exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Alert: $APPS_ID - Database Sessions Consuming High TEMP Tablespace on $HOST_NAME" $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

  sqlplus -s '/as sysdba' <<EOF

  set echo off
  set feedback off
  set lines 1000
  col bytes for 999999999999999.99
  col value for 999999999999999.99
  set long 2000000
  set pagesize 300
  set linesize 300
  col SQL_FULLTEXT for a9999
  SET MARKUP HTML ON SPOOL ON -
  HEAD '<title></title> -
  <style type="text/css"> -
     table { background: #eee; } -
     th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
     td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
  </style>' TABLE "border='1' align='left'" ENTMAP OFF


  spool $OUT_FILE


  PROMPT Hi Team,

  PROMPT
  PROMPT Please check below database sessions which are consuming more than $THRESHOLD MB of TEMP Tablespace:
  PROMPT


  select * from (
  SELECT   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
     S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
     COUNT(*) sort_ops
  FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
  WHERE    T.session_addr = S.saddr
  AND      S.paddr = P.addr
  AND      T.tablespace = TBS.tablespace_name
  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.client_identifier,
     S.program, TBS.block_size, T.tablespace,S.MACHINE)
       where MB_USED > $THRESHOLD;

  SPOOL OFF
  SET MARKUP HTML OFF
  exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Alert: $APPS_ID - Database Sessions Consuming High TEMP Tablespace on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "There are no high temp usage sessions" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> <Used in MB>

$ cd $HOME/DBA_MON/bin
$ sh TempTsUsage.sh ORCL 200

This script will send the notification with database session details which are consuming 

more than 200MB of TEMP tablespace.