Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> sscript to send mail when tablespace free < 15%
After seeing our tablespaces filling up time and again, here's the
script I wrote to monitor their free space. Works for both fixed size
and autoextensible tablespaces.
Thought I'd share.
Kind regards,
Herta
#!/bin/bash
#+
# Description
# ===========
# script to monitor the free tablespace of the running databases
# sends a mail listing the tablespaces and their free% if free% < 15
#
# assumes that
# - ORACLE_HOME has been defined
# - the script is run from an account with dba privs
#
# Modification History
# ====================
# Date | Name | Modification Description
# ------------+------------------------
+---------------------------------------#_
# 14-Sep-2007 | Herta Van den Eynde | initial version
# | |
#+
# initializations
# BFN = this script's basename
# DBI = database instance
# DBL = database instance list
# FS = free space
# MR = mail message recipient
# MS = mail message subject
# REC = record
# TOF = temporary output file
#_
typeset -i FS=0
MR="my.mail_at_comp.dom"
BFN=`basename $0`
TOF1=/SYS/LOC/ORAadm/log/${BFN%.*}.1.`date +%Y%m%d`
TOF2=/SYS/LOC/ORAadm/log/${BFN%.*}.2.`date +%Y%m%d`
#+
# redirect output to $TOF1
#_
exec 3>&1
exec > $TOF1
#+
# get list of active databases
#_
DBL=$(ps auxw | grep smon | egrep -v -e ASM -e grep | while read REC;
do
echo ${REC##oracle*_}
done)
#+
# get tablespace_name + free %)
# if tablespace has no free extents, it will not show up in
dba_free_space
#
# if extensible = 'NO' then free% =
# current_free * 100 / current_size
# if extensible = 'YES' then free% =
# ( max_size - ( current_size - current_free )) * 100 / max_size
#_
for DBI in $DBL; do
export ORACLE_SID=$DBI
SO=$( sqlplus -s << EOF
/as sysdba SET FEEDBACK OFF HEADING OFF SPOOL $TOF2 SELECT ddf.tablespace_name , ddf.extensible , ROUND( GREATEST( NVL( dfs.current_free, 0 ), ddf.max_size -( ddf.current_size - NVL( dfs.current_free, 0 ))) * 100 / GREATEST( ddf.current_size, ddf.max_size ), 0 ) "free%"
FROM ( SELECT tablespace_name , MAX( autoextensible ) as extensible , ROUND( SUM ( bytes )/1024/1024, 2) as current_size , ROUND( SUM ( maxbytes )/1024/1024, 2) as max_size FROM dba_data_files GROUP BY tablespace_name ORDER BY tablespace_name ) ddf , ( SELECT tablespace_name , ROUND( SUM( bytes )/1024/1024, 2 ) as current_free FROM dba_free_space dfs GROUP BY tablespace_name ORDER BY tablespace_name ) dfs WHERE dfs.tablespace_name = ddf.tablespace_name(+); SPOOL OFF
cat $TOF2 | while read TS E FS ; do
if [ -n "$TS" ]; then
if [ $FS -lt 15 ]; then echo "database $DBI tablespace $TS extensible $E free% $FS" fi
#+
# terminate output to $TOF1
#_
exec 1>&3 3>&-
#+
# send mail based on test results
#_
MH="Tablespace check failed on `hostname -s` - checked $( echo $DBL |
wc -w ) dbs"
if [ -s $TOF ]; then
MH="Tablespace check failed on `hostname -s` - checked $( echo $DBL
| wc -w ) dbs"
else
MH="Tablespace check OK on `hostname -s` - checked $( echo $DBL | wc
-w ) dbs"
fi
mail -s "$MH" "$MR" < $TOF1
#+
# clean up and end processing
#_
if [ -n $TOF1 ]; then
find `dirname $TOF1` -name "${BFN%.*}.*" -atime +28 -exec rm {} \;
fi
exit Received on Sun Sep 16 2007 - 17:57:20 CDT
![]() |
![]() |