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