DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Optimize segments for all tables in schema. 
# Version:      2018.10.01
# Dependencies: inc_system.sh, inc_sqlplus.sh
# Usage: segmaint_auto.sh <ORACLE_SID>
# Method used per Oracle's best practices on Segment maintenance detailed in MOS Doc ID 242090.1.
# 1. Shrink table but don't change change HWM (High Water Mark).
#    Being an online operation, this method has the shortest DML locks.
# 2. Move HWM.

######################################
# 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
usrMaxRunTime=180; # No new action if Elapse greater than this value (in minutes).

# Comma delimited list of Schema names. Ex: "SCHEMA1,SCHEMA2"
usrSchemaLst="\
ACTIVITYMANAGER,AWBATCH,BI,CCM,COREUSER,DDC,DMPDATA,DMPDATA2,MAPPER,MARKETVISION,\
OSIBANK,OSIEXTN,OSIOAS,PAYRSWAPSAVE,PORTAL,SAF,SAFUSER,SCCU,SYSMETA,WFINST";

# Comma delimited list of tables to skip. Ex: "SCHEMA1.TBL1,SCHEMA2.TBL1,SCHEMA2.TBLR2"
usrIgnoreTables=""
#usrIgnoreTables="\
#OSIBANK.ACTV,\
#OSIBANK.RTXN,\
#OSIBANK.RTXNAGREEMENT,\
#OSIBANK.RTXNBAL,\
#OSIBANK.RTXNFUNDTYP,\
#OSIBANK.RTXNSTATHIST,\
#OSIBANK.WH_ACCTCOMMON,\
#OSIBANK.WH_ACCTDEPOSIT,\
#OSIBANK.WH_ACCTLOAN \
#"

######################################
# Init Script Actions\Functions\Vars #
######################################
source $DIR_LIB/inc_sqlplus.sh

# Process CmdLine Parameters
if ! [[ -z "$1" ]]; then export ORACLE_SID="$1"; fi
if [[ -z "$ORACLE_SID" ]]; then echo "Invalid ORACLE_SID"; exit; fi;

rm $usrTmpDir/$sBaseName.???.t?? > /dev/null 2>&1; # Sweep tmp files from incompleted script.
IFS=","
isTableInIgnoreList() {
  local nRetVal=0
  local pTable="$1"
  shopt -s nocasematch
  for i in ${usrIgnoreTables[

  do
     if "$pTable" == ?; then
        nRetVal=1
     fi
  done
  shopt -u nocasematch
  echo $nRetVal

}

1. Start #

clear; log "$sSLog" "$sFullName Started"; linesep SysVars_show printf "ORACLE_SID: $ORACLE_SID\n" linesep "="

1. Process Loop

for s in $usrSchemaLst; do

   log "$sSLog" "[Schema: $s]"

   # Init Tmp File
   nRnd=`shuf -i100-999 -n1`
   sSQLPlusInit="set feedback off linesize 256 pagesize 0 trimspool on verify off"
   fTmpSQL="$DIR_TMP/$me.$nRnd.tmp"; fTables="$DIR_TMP/$me.$nRnd.tbl"

   # Create Tables Script
   sSQL="SELECT owner || '.' || table_name FROM dba_tables"
   sSQL="$sSQL WHERE owner='$s' AND iot_type IS NULL"
   sSQL="$sSQL ORDER BY table_name;"

   cat > $fTmpSQL <<-EOF
   $sSQLPlusInit
   spool $fTables
   $sSQL
   spool off
   exit

EOF

   # Create Tables File
   CMD="$($ORACLE_HOME/bin/sqlplus / as sysdba @$fTmpSQL)"
   log "$sSLog" "Tables File Created: $fTables"

   # Run SHRINK Cmds
   while read t; do
       # usrMaxRunTime Check
       if $ -ge $usrMaxRunTime ?; then
          log "$sSLog" "Breaking: usrMaxRunTime exceeded ($usrMaxRunTime)"
          rm $fTmpSQL > /dev/null 2>&1;rm $fTables > /dev/null 2>&1;
          break
       fi

       # Table Ok to Process?
       isTableOK=$(isTableInIgnoreList $t)
       if $isTableOK -eq 1 ?; then
          log "$sSLog" "Skipping: $t"
          continue
       fi

       # Enable Row Movement
       sSQL="ALTER TABLE $t ENABLE ROW MOVEMENT;"
       log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")

       # Shrink Table but dont Change High Water Mark (HWM)
       sSQL="ALTER TABLE $t SHRINK SPACE COMPACT;"
       log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")

       # Reset HWM
       sSQL="ALTER TABLE $t SHRINK SPACE;"
       log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")

   done <$fTables

   # Housekeeping
   rm $fTmpSQL > /dev/null 2>&1;rm $fTables > /dev/null 2>&1;

   # usrMaxRunTime Check
   if $ -ge $usrMaxRunTime ?; then
      log "$sSLog" "Breaking: usrMaxRunTime exceeded ($usrMaxRunTime)"
      rm $fTmpSQL > /dev/null 2>&1;rm $fTables > /dev/null 2>&1;
      break
   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

@]