Tablespace check script for Oracle E-Business Suite R11&R12

Tablespace check script for Oracle E-Business Suite R11&R12

In this article, i will explain how we can check tablespace size wiht scriptsAs i Dba or Apps Dba, we should check tablespace size to dont have any issue when they reach %100 usage. Here is the sample for make automatic check for tablespace usage

First of all; we will create custom env file for my script. I like to create env file because i can use same env(after edit) for other clients.

Here is my env file:

CUSTOMER=ABC
USER=oravis
ORACLE_SID=VIS
ORACLE_HOME=/u01/VIS/visdb/10.2.0
SID_HOST=”$SID”_test
ALR_DATE=`date +%d%m%Y`
ALR_LOG_FILE=/u02/alert_errors_”$ALR_DATE”.log
DBA=”erp@xxx.com”

export CUSTOMER USER SID ORACLE_HOME SID_HOST ALR_DATE ALR_LOG_FILE DBA

As you can notice, this env uses for our ABC compan my dbtier owner is oravis, my SID is VIS,my linux machine hostname is test.You can change those information wiht yours

Tablespace check script:

Before start script please be notice i put my.env file under /u01 mount point

#My env file is set here

. /u01/my.env
ps -ef | grep -v grep | grep ora_pmon_$ORACLE_SID | wc -l | while read CONTROL
do

if [ "$CONTROL" -gt 0 ] ; then

sqlplus -s < tablespace.tmp
/bin/mailx -s “TABLESPACE ALERT for ${ORACLE_SID}” erp@xxx.com < tablespace.alert
fi
else
/bin/mailx -s “DB ALERT- ${ORACLE_SID} KAPALI” erp@xxx.com< message.txt
fi
done

If i put this script to my crontab it will run and check tablespace usage. If size become %80 then it will send me e-mail. You can change ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) <=20 parameter for your need. I mean if you need alert for usage become %95, than put 5 instead of 20.