REM This script may be used as a cronjob to coalesce the tablespaces periodically. This is especially useful if all your tablespace pctincrease value is set to 0. Uses /etc/oratab to read all instance names, and operating system authentication to log into the database. Sun users change it to /var/opt/oracle/oratab

#!/bin/ksh
# Since pctincrease on tablespaces are 0, coalesce freespace
#
#
# 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 <
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