DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, November 27, 2014

Tablespace free space check script

#!/bin/bash
###############################################################################
# File Name   : tablespace_free_space.sh
# Author      : Vimal Raju
# Date        : 27/11/2014
# Description : Tablespace size check
#
###############################################################################
#############################################################
#
# Display usage infommation
#
usage () {

  echo " "
  echo " Checking the Tablespace free space "
  echo "       -h Display this message"
  echo "       -e specify the environment file with location (Mandatory) (Example : /home/oracle/db.env ) "
  echo "       -m specify the email address (Mandatory) (Example : test@gmail.com,test1@gmail.com ) "
  echo "       -p Specify the Percentage of the tablespace size check (default : 80 )"
  echo "       -l Specify the Log file location (default : /tmp )  "
  echo "     Example : tablespace_free_space.sh -e /home/oracle/db.env -m support@doyensys.com "

  exit 1

}

validate() {

  INDFILE=${LOG_LOC}/tablepace_size_check.ind
  # To check if the indicator file is there
  if [ -f ${INDFILE} ]; then
    echo "Indicator file ${INDFILE} exists!"
    exit 1
  else
    touch ${INDFILE}
  fi

  if [ ${#ENV_FILE} -eq 0 ]; then
    echo "No Environment file name specified!"
    usage
  fi

  if [ ${#MAIL_TO} -eq 0 ]; then
    echo "No Mail address specified!"
    usage
  fi

  if [ -f ${LOG_LOC}/tablespace_trigger_tmp.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_trigger_tmp.log
  fi

  if [ -f ${LOG_LOC}/tablespace_trigger.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_trigger.log
  fi

  if [ -f ${LOG_LOC}/tablespace_check.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_check.log
  fi

}


tablespace() {

sqlplus -s / as sysdba <<EOF
set feed off
spool ${LOG_LOC}/tablespace_check.log
col tablespace_name for a20
Select * from (select  tbs.tablespace_name,
        tot.bytes/(1024*1024) "Total Space in MB",
        round(tot.bytes/(1024*1024)-  sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
        round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
        round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) Pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1) where Pct > ${PERCENTAGE}  ;
spool off
EOF

trig=`cat ${LOG_LOC}/tablespace_check.log|wc -l`
check_tmp=`cat ${LOG_LOC}/tablespace_trigger_tmp.log`

if [ "${trig}" != 0 ]
then
echo C > ${LOG_LOC}/tablespace_trigger.log
elif [ "${check_tmp}" = 'T' ]
then
echo N > ${LOG_LOC}/tablespace_trigger.log
fi

check_crit=`cat ${LOG_LOC}/tablespace_trigger.log `
if [ "${check_crit}" = 'C' ]
then
cat ${LOG_LOC}/tablespace_check.log|mailx -s "Critical : Tablespace Total allocated > ${PERCENTAGE}" $MAIL_TO
echo T > ${LOG_LOC}/tablespace_trigger_tmp.log
elif [ "${check_crit}" = 'N' ]
then
cat ${LOG_LOC}/tablespace_check.log|mailx -s "Normal : Tablespace Total allocated < ${PERCENTAGE}" $MAIL_TO
echo "" > ${LOG_LOC}/tablespace_trigger.log
echo "" > ${LOG_LOC}/tablespace_trigger_tmp.log
fi

}


PERCENTAGE=80
LOG_LOC=/tmp
ENV_FILE=
MAIL_TO=

while getopts :hp:l:e:m: opt $*
    do
      case $opt in
        h) usage
           ;;
        p) PERCENTAGE=${OPTARG}
           echo PERCENTAGE set to ${PERCENTAGE}
           ;;
        l) LOG_LOC=${OPTARG}
           echo LOG_LOC set to ${LOG_LOC}
           ;;
        e) ENV_FILE=${OPTARG}
           echo ENV_FILE set to ${ENV_FILE}
           ;;
        m) MAIL_TO=${OPTARG}
           echo MAIL_TO set to ${MAIL_TO}
           ;;
        *) echo "******************************************************************"
           echo "${JOBNAME}: Invalid option found on command line: ${OPTARG}"
           echo "******************************************************************"
           usage
           ;;
      esac
    done


validate

. $ENV_FILE

tablespace

if [ -f ${INDFILE} ]; then
      rm -f ${INDFILE}
fi

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.