On Sep 18, 2:34 am, herta <herta.vandeney..._at_gmail.com> wrote:
> 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
What do you mean by "it's not evident to check them every day"?
Received on Tue Sep 18 2007 - 07:46:00 CDT
