You have your choices when monitoring tablespaces in an oracle system. You can leverage OEM or you can write a script. Its a debatable choice. But if your levering RDS in the cloud(aws), your options may be limited.

 

If that’s the case, take a look at the below. The script allows you to pick a size threshold, discover those tablespaces, and alert you and team of tablespaces nearing their death.

oracle@typoraracdg1-n02:NotSet> less monitorUpdate.sh
#####################################################################
## check_tablespace.sh ##
##
#####################################################################
###!/bin/bash
##
######## Other variables #################
##
DBA=alert@email.com
DATABASE=TEST01
datevar=$(date)
datevar2=$(date ‘+%Y-%m-%d-%H-%M’)
##
####### End of configuration
##
sqlplus -s “<schema>/<password>@awsdb2” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /home/oracle/temp/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,99999′)||’ %’ PERCENT_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 /* WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’, ‘SYSAUX’ , ‘TEMP’ , ‘USERS’ , ‘UNDOTBS1’ , ‘UNDOTBS2’) */
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES /* WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’, ‘SYSAUX’ , ‘TEMP’ , ‘USERS’ , ‘UNDOTBS1’ , ‘UNDOTBS2’)*/
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off
exit
SQL1
LINECOUT=$(cat tablespace.alert | wc -l)
if [ $LINECOUT -gt 0 ]
then
echo Tablespace less than 10% free on ${DATABASE}.Please add space as necassary >> /home/oracle/temp/tablespace.tmp
cat /home/oracle/temp/tablespace.alert >> /home/oracle/temp/tablespace.tmp
mailx -s “Tablespace percent usage for ${DATABASE} at $datevar” $DBA < /home/oracle/temp/tablespace.tmp
#####mv /disk1/tablespace.tmp /disk1/tablespace_$datevar2.alert
rm /home/oracle/temp/tablespace.alert
rm /home/oracle/temp/tablespace.tmp
fi