SQL Script for oracle tablespace monitoring

Use the below script and alter the threshold,emailadddress and schema username and password for your systems and run it in crontab to send you alerts when the tablespace exceeds the threshold value. We know we can do this by CCMS setup but this is good to have functionality.

DBALIST=”email address”
sqlplus -s <<!
SAPschemausername/password
set feed off
set linesize 100
set pagesize 200
spool tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999,99′) “USED(MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE(MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999,999’) “TOTAL(MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999,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 FREE_SPACE < (size below which it should alert,exe 5000mb)

AND T.TABLESPACE_NAME = ‘tablespacename you want to monitor’;
spool off
exit
!
if [ `cat tablespace.alert|wc -l` -gt 0 ]
then
cat tablespace.alert > tablespace.tmp
mailx -s “TABLESPACE ALERT-PLEASE TAKE ACTION ” $DBALIST < tablespace.tmp
fi