E-mail alert when Oracle tablespaces gets low

You can run this script as a any Oracle db user and as a oracle Linux user, and you can put this script to your crontab for daily checks. Also, you will get email when DB is not started because of any reason.

#!/bin/sh
source /home/oracle/.bash_profile

############ VARIABLES ##############
SERVER="MyServer - `ip addr | grep 'state UP' -A2 | grep 'inet ' | tail -n1 | awk '{print $2}' | cut -f1  -d'/'`"
HTML_REPORT="/tmp/tablespace_report.html"
ALLOWED_TABLESPACE_USAGE_IN_PERCENTAGE=$1
MAIL_TO="mymail@example.com"
#####################################

# Ako nema ulaznih parametara postavi 90% i izbaci alert
if [ $# -lt 1 ]
then
	echo "You didn't set percentage of used tbs space as input parameter. We will use 90%"
	echo "To put this paramtere, 85% of used space for example, run script on this way:"
	echo "$0 85"
	#----------------------------------------#
	ALLOWED_TABLESPACE_USAGE_IN_PERCENTAGE=90
fi

sqlplus -S -M "HTML ON TABLE 'BORDER="1" CELLSPACING="0"'" '/ as sysdba' <<EOF > ${HTML_REPORT} 2>/dev/null
set pagesize 32000
set feedback on
select b.tablespace_name Name,
       TO_CHAR(b.TABLESPACE_SIZE,'999999990.9') "Size(MB)",
       TO_CHAR((b.TABLESPACE_SIZE-nvl(a.TOTAL_FREE,0))*100/b.TABLESPACE_SIZE,'99990.9') "Used(%)",
       TO_CHAR(b.TABLESPACE_SIZE-nvl(a.TOTAL_FREE,0),'999999990.9') "Used(MB)",
       TO_CHAR(nvl(a.TOTAL_FREE,0),'999999990.9') "Free(MB)",
       decode(b.AUTOEXTENT,0,'   NO','  YES') "AutoExt"
from
(
SELECT tablespace_name,
       SUM(bytes)/1024/1024 TOTAL_FREE
FROM dba_free_space
GROUP BY tablespace_name
) a,
(
SELECT c.tablespace_name,
       SUM(c.bytes)/1024/1024 TABLESPACE_SIZE,
       (select count(*) from dba_data_files d where d.tablespace_name=c.tablespace_name and d.autoextensible='YES') AUTOEXTENT
FROM dba_data_files c
GROUP BY c.tablespace_name
) b
where b.tablespace_name=a.tablespace_name(+)
  and (b.TABLESPACE_SIZE-nvl(a.TOTAL_FREE,0))*100/b.TABLESPACE_SIZE >= ${ALLOWED_TABLESPACE_USAGE_IN_PERCENTAGE}
order by "Used(%)" desc;
exit
/
EOF

if [ $(grep -ir "no rows selected" ${HTML_REPORT} | wc -l) -ne 1 ]; then
	echo "Tablespaces space gets low on ${SERVER}" |
	 mail -s "${SERVER}: Tablespace usage >${ALLOWED_TABLESPACE_USAGE_IN_PERCENTAGE}%" -a ${HTML_REPORT} $MAIL_TO
fi

exit 0

Run sctipt on this way:

# For (in example) 80% of used space in tbs as alert trigger run:
./tbs_script.sh 80

# If there is no input parametar, we will use 90% as a alert triger