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