DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Preselect most used tables to slightly improve performance (5-15%).
# 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
usrTables="\
OSIBANK.WH_ACCTCOMMON \
OSIBANK.WH_ACCTDEPOSIT \
DMPDATA.LNHISTORY \
DMPDATA.SHHISTORY \
OSIBANK.ACTVSUBACTV \
OSIBANK.WH_ACCTLOAN \
OSIBANK.CARDTXN \
OSIBANK.PRICACCTLIST \
OSIBANK.RPT_SORT_PROCESS \
OSIBANK.EXTTXNMSG \
OSIBANK.RTXN \
OSIBANK.FILERECORD \
OSIBANK.RTXNBAL \
OSIBANK.ACTV \
OSIBANK.RTXNSTATHIST \
OSIBANK.ACCTBALHIST \
OSIBANK.RTXNAGREEMENT \
OSIBANK.ACCTAPPLHIST \
DMPDATA.POSTINGFILE \
OSIBANK.APILOG \
"


######################################
# Init Script Actions\Functions\Vars #
######################################
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

# Processing Loop
for t in ${usrTables[

do

   log "$sSLog" "Processing: $t"
   sSQL="SELECT count(*) FROM $t;"
   sRetVal=$(xsql "$sSQL")
   log "$sSLog" "$sSQL - $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

@]