Oracle Tablespace Check

Here's a script that checks the tablespace utilization on a database, and sends a mail if it crosses a threshold

#!/bin/bash
# Tablespace check script 
# Author: Nabeel Moidu
# Website: nmkuttiady.blogspot.com
# Set threshold here
THRESHOLD=70
# Set the Oracle user here
ORACLE_USER=ora10g
# Set the mail id of the DBA
DBA="dba@organization.tld"

# Get output of tablespace usage on the database
su - $ORACLE_USER -c "sqlplus -s \"/ as sysdba\" < /backup/script/tablespace_usage.sql > /tmp/output.txt "

# Remove blank lines from file 

cat /tmp/output.txt | grep -v ^$ | grep -v "no rows selected" > tablespaces.txt

# If no tablespace crosses threshold, the file will be empty. So send mail only if file not empty

if test -s "tablespaces.txt"

then

        # Prepare the mail
        echo "The following tablespaces have exceeded the 60 percent threshold" > mail.txt

        #Send the mail with the hostname in subject
        HOST=`hostname`
        SUBJECT="Daily Tablespace Check - "$HOST
        echo $SUBJECT
        mail -s "$SUBJECT" $DBA < mail.txt

fi

This is the SQL query to get the tablespace output

set pages 700
set linesize 1000
select t.tablespace_name,total_MB,round((total_MB-free_MB),2) used_MB, round(free_MB,2) free_MB,round(((total_MB-free_MB)/total_MB)*100,2) per_used,round((free_MB/total_MB)*100,2) per_free from (select tablespace_name,sum(bytes/1024/1024) free_MB from dba_free_space group by tablespace_name) f,(select tablespace_name,sum(bytes/1024/1024) total_MB from dba_data_files group by tablespace_name) t where f.tablespace_name(+)=t.tablespace_name order by 5;