DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Rebuild select set of indexes.
# Version:      2019.20.27
# Dependencies: inc_system.sh,inc_sqlplus.sh

######################################
# Preamble: 2018.10.01               #
######################################
set -a; DIR_SCRIPTS="/u01/app/scripts"; source $DIR_SCRIPTS/inc_system.sh
sFullName=$(basename "$0"); me=${sFullName%.*}
sSLog="$DIR_LOGS/$me.sess.log"; sHLog="$DIR_LOGS/$me.hist.log"; > $sSLog
if [[ $MAINT_WINDOW -eq 1 ]]; then printf "Maintenance Window Detected - Exiting\n"; exit; fi

# User Vars
usrOraSid="dnasolo";                # ORACLE_SID
usrSchema="OSIBANK";                # Schema of indexes to rebuild
usrWhere="index_name LIKE 'WH_%'";  # WHERE string to use.
usrIdxParallel=24;                  # PARALLEL value to use on index rebuilds.
usrMode=1;                          # 0=Show cmds (but dont run them). 1=Run the cmds.

######################################
# Init Script Actions\Functions\Vars #
######################################
source $DIR_LIB/inc_sqlplus.sh
export ORACLE_SID="$usrOraSid";


######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show
printf "ORACLE_SID:     $ORACLE_SID\n"
printf "usrSchema:      $usrSchema\n"
printf "usrIdxParallel: $usrIdxParallel\n"
printf "usrMode:        $usrMode\n"
linesep "="
sleep 3



# Create Cmd to Rebuild Indexes
unset IFS
sSQL="SELECT trim(tablespace_name) || ',' || trim(index_name) FROM dba_indexes "
sSQL="$sSQL WHERE $usrWhere " 
sSQL="$sSQL AND owner='$usrSchema' "
sSQL="$sSQL AND   index_type='NORMAL' "
sSQL="$sSQL AND   index_name NOT LIKE 'SYS%' "
sSQL="$sSQL ORDER BY index_name;"
###echo $sSQL; pause
aINDEXES=$(xsql "$sSQL");


# Processing Loop
for ts_idx in ${aINDEXES[

   IFS=, read -a aTS_IDX <<< "$ts_idx"
   sTablespace=${aTS_IDX[0]}
   sIndex=${aTS_IDX[1]}

   sSQLIdx="ALTER INDEX $usrSchema.$sIndex REBUILD PARALLEL $usrIdxParallel ONLINE NOLOGGING TABLESPACE $sTablespace;"
   log "$sSLog" "$sSQLIdx"
   if $usrMode -eq 1 ?; then
      CMD=$(xsql "$sSQLIdx");
   fi

   # Reset PARALLEL
   sSQLResetPar="ALTER INDEX $usrSchema.$sIndex PARALLEL 1;"
   log "$sSLog" "$sSQLResetPar"
   if $usrMode -eq 1 ?; then
      CMD=$(xsql "$sSQLResetPar");
   fi

done

1. End #

log "$sSLog" "$sFullName Ended [Elapse Time: $(elapse)]"; echo $(linesep "=") >> $sHLog; cat $sSLog >> $sHLog; tail -32768 $sHLog > $sHLog.tmp; mv $sHLog.tmp $sHLog @]