Script to query the database for tablespaces and estimates and space utilization

Description:    This script queries your database for tablespaces and estimates their space utilization like allocated space, used space, free space available and percentage of used space.
The script can also search if %used space is more than 80, send an email to concerned, and log details to a file.
Code:

#Description:
# This script queries your database for tablespaces and estimates their space utilization like allocated space, used space, free space available and percentage of used space.
# The script can also search if %used space is more than 80, send an email to concerned, and log details to a file.

#You should not have to pass any parameter. It will search all the databases available in the oratab file

#Code:
#!/bin/sh
#
#Script name: estimate_space.sh
#
#Comments: Script checks for disk space utilisation of Tablespces in all DBs available in the oratab file
#sends email messages to concerned if any tablespace is utilised 80 percent of allocated space.
#-------------------------------------------------------------------------------------------------
# Revision Log
#
# 00/00/00 : Name of modifier - description of Modifications
#-------------------------------------------------------------------------------------------------
#
#Variables declared
CWD=/opt/oracle
DIR=/var/opt/oracle
export CWD DIR

#Variables picked up from the environment
HOST=`uname -a | awk '{print $2}'`
ORACLE_HOME=`grep -v '^#' $DIR/oratab | awk -F':' '{print $2}' | tail -1`
export HOST ORACLE_HOME

grep -v '^#' $DIR/oratab | awk -F':' '{print $1}' >/tmp/xyz

for SID in `cat /tmp/xyz`
do
ORACLE_SID=$SID;
export ORACLE_SID

#Querry database for all tablespaces and their disk space utilisation and makes a list
$ORACLE_HOME/bin/sqlplus system/manager /tmp/tbs2.list
grep -v '^(c)' /tmp/tbs2.list > $CWD/tablespace$SID.list

cat $CWD/tablespace$SID.list|while read LINE
do
TBS=`echo $LINE | awk -F':' '{print $1}'`
RATIO=`echo $LINE | awk -F':' '{print $5}'`

#Checks if any tablespace having used space for more than 80%
if [ "$RATIO" -ge 60 ]
then

#If this entry is there more than 2 times in the log, sending email will be ignored
y=`cat $CWD/tablespace$SID.log | egrep -ch $TBS`
if [ $y -ge 2 ]
then
#continue
echo "Problem still exists in the above tablespace in $SID -
delete the above entries after correcting Database `date '+%m/%d/%y %H:%M'`" >>$CWD/tablespace$SID.log
else
echo "Tablespace $TBS in $SID is filled around $RATIO percent in $HOST `date '+%m/%d/%y %H:%M'`" >>$CWD/tablespace$SID.log
echo "Tablespace $TBS in $SID is filled around $RATIO percent in $HOST" | /usr/bin/mailx -s "check $CWD/tablespace$SID.list" oracle;
fi
fi

done
done