DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Optimize segments from list of tables.
# Version:      2018.10.01
# Dependencies: inc_system.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
usrORACLE_SID="dnasolo"
usrTables="\
OSIBANK.RTXN \
OSIBANK.RTXNAGREEMENT \
OSIBANK.RTXNBAL \
OSIBANK.RTXNFUNDTYP \
OSIBANK.RTXNSTATHIST \
OSIBANK.WH_ACCTCOMMON \
OSIBANK.WH_ACCTDEPOSIT \
OSIBANK.WH_ACCTLOAN \
"

######################################
# Init Script Actions\Functions\Vars #
######################################

# Process Cmd Line Param
if [[ -z "$1" ]]; then  ORACLE_SID="$usrORACLE_SID"; else ORACLE_SID="$1"; fi

# xsql
xsql() {
   local nRnd=`shuf -i100-999 -n1`; local sSQL="$1";
   local sSQLPlusInit="set feedback off linesize 256 pagesize 0 trimspool on verify off";
   local fScript="$DIR_TMP/xsql.$nRnd.tmp"; local fSpool="$DIR_TMP/xsql.$nRnd.spool";

   cat > $fScript <<-EOF
   $sSQLPlusInit
   spool $fSpool
   $sSQL
   spool off
   exit
EOF

   # Run SQL Script
   CMD=$($ORACLE_HOME/bin/sqlplus / as sysdba @$fScript $fSpool)
   sValue=$(cat $fSpool| awk '{gsub(/^ +| +$/,"")} {print "" $0 ""}'); 
   echo $sValue; rm $fScript; rm $fSpool;
}

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

# Processing Loop
for t in ${usrTables[

do

   # 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

1. End #

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

@]