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 <<!
apps/apps
set feed off
set linesize 100
set pagesize 200
spool /u01/tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED (MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE (MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL (MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′)||’ %’ PER_FREE
FROM   (
SELECT       TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) <=20;
spool off
exit
!
if [ `cat tablespace.alert|wc -l` -gt 0 ]
then
#cat tablespace.alert -l tablespace.alert > 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.