Automated script to rebuild Specified TABLE and its INDEXES


#!/bin/shORACLE_SID=${1}
ORAENV_ASK=NO. oraenv
######################################################################### Variable Declaration######################################################################
export ORACLE_HOME=${2}
export OWNER=${3}
export TABLE=${4}
export DEGREE=8 #Adjust the parallel degree as per your requirement & available CPU
export SCRIPT_DEST=/home/oracle/AUTO/rebuild
export REBUILD_SQL=${SCRIPT_DEST}/${OWNER}_${TABLE}_rebuild.sql
export LOG=${SCRIPT_DEST}/${OWNER}_${TABLE}_LOG.txt
export now=`date '+%d-%b-%y %Hh-%Mm-%Ss'`
export MAIL_LIST="emailid1@domain.com,emailid2@domain"

######################################################################### Table Rebuild Code Starts Here..######################################################################
echo ${OWNER}.${TABLE} Rebuild Started at $now >> ${LOG}echo spool ${LOG} append >> ${REBUILD_SQL}
echo "alter table ${OWNER}.${TABLE} enable row movement;" >> ${REBUILD_SQL}echo "alter table ${OWNER}.${TABLE} move parallel ${DEGREE};" >> ${REBUILD_SQL}echo "alter table ${OWNER}.${TABLE} disable row movement;" >> ${REBUILD_SQL}
sqlplus -s -l / as sysdba << EOFSET VERIFY OFF pages 0 heading off FEEDBACK OFF VERIFY OFF ECHO OFF PAGESIZE 0spool ${REBUILD_SQL} appendselect 'alter index '||owner||'.'||index_name||' rebuild online parallel ${DEGREE};' from dba_indexes where table_name = '${TABLE}';select 'alter index '||owner||'.'||index_name||' parallel 1;' from dba_indexes where table_name = '${TABLE}';spool offEXIT;EOF
echo spool off >> ${REBUILD_SQL}
sqlplus -s -l / as sysdba << EOF@${REBUILD_SQL}EXIT;EOF
export now=`date '+%d-%b-%y %Hh-%Mm-%Ss'`echo ${OWNER}.${TABLE} Rebuild Completed at $now >> ${LOG}
/bin/mailx -s "${ORACLE_SID} :: ${OWNER}.${TABLE} rebuild completed at $now" ${MAIL_LIST} < ${LOG}
######################################################################### Table Rebuild Code Ends Here..######################################################################

######################################################################### Deleting Logs..######################################################################rm -f ${REBUILD_SQL} ${LOG}