Title: Tablespace Saturation Alert

Author:Jeffrey Tarnok, a senior systems engineer and DBA for The Technical Resource
Connectionin Tampa, Florida.

The Tablespace Saturation Alert is a unix korn shell script that calls a sql script reporting tablespace utilization and sends an
alert via email for tablespaces saturated greater than a specified percentage. The script can easily be run as a cron job on unix
systems to proactively notify DBAs about tablespace utilization and imminent saturation problems. Utilization percent is an
arbitrary number simply defined in the script. The script uses twos views created as sys to provide summary byte information.
The views can be replaced with a summary report but I find them handy for other report generation.

First the script that creates the two required views executed as sys:

rem
rem  create_space_views.sql
rem
rem  Purpose:
rem     This script will create required views for space reporting
rem     This script should be run as "SYS"
rem
rem  Author:    Jeff Tarnok
rem  Date:      Jan. 1999
rem  Version History:
rem     Version 1.00.00
rem
create or replace view space_total as select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name;
create or replace view space_used as select tablespace_name, sum(bytes) bytes from dba_extents group by tablespace_name;

Next the sql script that reports tablespace utilization:

rem
rem  space.sql
rem
rem  Purpose:
rem     This script reports tabespace utilization using views created with
rem     create_space_views.sql.
rem
rem  Author:    Jeff Tarnok
rem  Date:      Dec. 1998
rem  Version History:
rem     Version 1.00.00
rem
set feedback off
column total heading "Total MB" format 99999
column used heading "Used MB" format 99999
column percent heading "% Utilization" format 999
select d.tablespace_name, round(d.bytes/1048576) total, nvl(round(f.bytes/1048576), 0) used, nvl(f.bytes/d.bytes*100, 0) percent from sys.space_total d, sys.space_used f where d.tablespace_name=f.tabl
espace_name (+)
/
exit


Finally the korn shell script that parses the space.sql output and determines if an alert is required:

#!/bin/ksh
# space.ksh
#
#  Purpose:
#       This script will call space.sql and check for tablespace utilization
#       > than 90%. Alerts can be sent via email for saturated tablespaces
#       Leaves space.out file to review all tablespace utilization
#
#  Author:      Jeff Tarnok
#  Date:        January 1999
#  Version History:
#       Version 1.00.00
#
# Get hostname of server machine
HOST=`hostname`
# Set mail recipient to DBA list
DBA=tarnokj@trcinc.com
#
# Call space.sql to check tablespace utilization
#
# Replace username/password with valid account
# Change the integer argument to any appropriate percentage
sqlplus user/password @space | /usr/bin/nawk 'int($4) > 90 {print $1, "@", $4}' > space.out
# Test if output is a non-zero byte file
#
        if [ -s space.out ]
        then
# If non-zero byte file mail warning to DBA recipient
        mailx -s "Tablespace Saturation Warning on $HOST" $DBA < space.out
        fi