#!/bin/ksh
# Since pctincrease on tablespaces are 0, coalesce freespace
# 
# Biju Thomas - 02/04/98
#
#
# Read /etc/oratab file to get the instance names on this machine and
# Oracle Home directory
#
#
cat /etc/oratab | while read LINE
do
  case $LINE in
  \#*)            ;;      #comment-line in oratab
  *)
  #       Proceed only if third field is 'Y'.
  if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
    ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
    export ORACLE_SID
    ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
    export ORACLE_HOME
    wlogfile=/tmp/tscoalesce.${ORACLE_SID}
    SQLPLUS=${ORACLE_HOME}/bin/sqlplus
    echo "Start time : "`date` > $wlogfile
    $SQLPLUS -s /  >> $wlogfile <<EOF
       set echo off feedback off verify off pages 0 termout off
       spool /tmp/coalesce.sql
       SELECT 'alter tablespace ' || tablespace_name  || ' coalesce;'
       FROM   dba_tablespaces;
       spool off
       set feedback on verify on pages24 echo on termout on
       @/tmp/coalesce.sql
EOF
  fi
esac
done
#
# End of script