DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Refresh Materialized Views  
# 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
usrSQLPlusInit="SET ECHO ON"
usrConnString="connect BI/DQlOjb8eRXpVRel!@DNAPROD"
usrMViews="BI.MV_CONTACTINFORMATION BI.MV_FUNDEDLOANS_AUTO";


######################################
# Init Script Actions\Functions\Vars #
######################################
rm $DIR_TMP/$me.*.tmp
rm $DIR_TMP/$me.*.spool

# Functions
xsql_mv() {
   # Init Vars
   local nRnd=$(shuf -i100-999 -n1)
   local sMV="$1";
   local sSQLPlusInit="SET ECHO ON"
   local fScript="$DIR_TMP/$me.$nRnd.tmp";
   local fSpool="$DIR_TMP/$me.$nRnd.spool";

   cat > $fScript <<-EOF
   $sSQLPlusInit
   $usrConnString
   spool $fSpool
   EXEC dbms_mview.refresh('$sMV', method=>'C', atomic_refresh=>false);
   exit
EOF


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

   # End
   rm $fSpool
   rm $fScript
}


######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show

for v in ${usrMViews[

do

   log "$sSLog" "Refreshing: $v"
   sRetVal=$(xsql_mv "$v")
   log "$sSLog" "$sRetVal"

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

@]