Re: sscript to send mail when tablespace free < 15%

On Sep 17, 4:40 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> herta wrote:
> > 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.m..._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
> > EOF
> > ) # end of SO
>
> > 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
> > fi
> > done
> > echo
> > done
>
> > #+
> > # 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
>
> Oracle's Enterprise Manager already contains this functionality...plus
> much much more! But thanks for sharing.
>
> Cheers,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com

I know you can see this in OEM, but when you have well over 1000 tablespaces, it's not evident to check them every day.

Kind regards,

Herta Received on Tue Sep 18 2007 - 02:34:20 CDT