Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sscript to send mail when tablespace free < 15%

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

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Sun, 16 Sep 2007 21:40:09 -0500
Message-ID: <46eddd0e$0$7122$88260bb3@free.teranews.com>


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.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
> 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
dba_at_nospam.peasland.net
http://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 from http://www.teranews.com
Received on Sun Sep 16 2007 - 21:40:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US