Message-Id: <200201171232.AA26410@odb.odl.com>
Apparently-To: root

stty: tcgetattr: Not a typewriter
Not a terminal
stty: tcgetattr: Not a typewriter
stty: tcgetattr: Not a typewriter
Not a terminal
stty: tcgetattr: Not a typewriter
stty: tcgetattr: Not a typewriter


*************************************************
Cron: The previous message is the standard output
      and standard error of one of your cron commands.

#oracle path
OBIN="/u05/z05/app/oracle/product/7.3.4/bin"

# Path to configuration files
CFG_DIR="/u05/z05/app/oracle/product/7.3.4/db_mgmt/alert_scripts"

# List of non-default quotes from configuration file
QT=`cat $CFG_DIR/alert_tbs_quotes.cfg | awk '$1 !~ /#/ {print $1 " " $2 "~"}`

# Default limit of free space%
DFLT_PERC=20

i=0

add_msg()
{
  i=$(($i+1))

  MSG="${MSG}
$1 has $2% of free space, what is less than $3% safe quote.
It has to be expanded by ${5}MB from current ${4}MB to get above the quote.
"
}

# calculate data about tablespaces from Oracle
{
$OBIN/sqlplus -s $PWD <<EOF
set linesize 500
set pagesize 0
set verify off
set feedback off
select f.tablespace_name||' '||to_char(round(sum(free)/sum(total)*100))
||' '||to_char(sum(total))||' '||to_char(sum(free)) from
(select tablespace_name, file_id, sum(bytes) free from dba_free_space
 group by tablespace_name, file_id) f,
(select file# file_id, bytes total from v\$datafile) z
where z.file_id=f.file_id group by tablespace_name order by 1;
exit
EOF
} \
| \
{
while read TBS PERC SIZE FREE
do
  # trying to look for quotes in configuration file
  LMT=` print $QT | awk -v RS="~" -v T="$TBS" '{ if ( $1 == T )
   print $2}'`
  LMT=${LMT:-$DFLT_PERC}
  if (( $PERC < $LMT )); then
    add_msg "$TBS" "$PERC" "$LMT" "$(($SIZE/1024/1024))" "$(( ($SIZE*$LMT-$FREE*100)/(100-$LMT)/1024/1024))"
  fi
done
}

if [[ -n ${MSG:+1} ]]; then
MSG="This message was generated by alert notification system.
Tablespace(s) below has exceed free space quote and needs to be expanded.
You can specify special quote for particular tablespaces in
file $CFG_DIR/alert_tbs_quotes.cfg.
${MSG}
Have a nice day!"

/usr/bin/perl /u04/z04/home/email/send_email.perl  \
-from "OAPROD UNIX-BOX <odl@odl.com>" -to "$1" -subject "Tablespace(s) Alert" \
-text "$MSG"
fi