It is not tested, but I think it works. If you face problems, let me know.
There are several you can improve on it.
Code:
cat scriptDb.sh
############
#!/bin/ksh
ORACLE_SID=$1
a=`hostname`
PSDBA="xxxxxx"
# OS Definition
myPID=$$
# Files
sqlFile="<PathTo>/scriptDb.sql"
sqlplusOutputLog="/tmp/schema_${myPID}.log"
# Sleep time in seconds
maxSleepTime=300
loopSleepTime=5
countLoopSleetTime=0
nohup sqlplus -L @"${sqlFile}" "${sqlplusOutputLog}" 1>/dev/null 2>&1 &
while true
do
if [ ${countLoopSleetTime} -ge ${maxSleepTime} ]
then
pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
kill -15 ${pid}
break
fi
countLoopSleetTime=`expr ${countLoopSleetTime} + ${loopSleepTime}`
done
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
if [ $chk_err -ne 0 ]
then
mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA < "${sqlplusOutputLog}"
exit 0
fi
Code:
cat scriptDb.sql
############
SET VERIFY OFF
SET TIMING ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
spool &1
CONNECT system/system
show user
select instance_name from v$instance;
select TO_CHAR(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
exec dbms_stats.GATHER_SCHEMA_STATS (ownname=>'USER', block_sample=>true, granularity=>'AUTO', options=>'GATHER', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE auto', degree=>null, cascade=>SYS.DBMS_STATS.AUTO_CASCADE, no_invalidate=>SYS.DBMS_STATS.AUTO_INVALIDATE,forc e=>true);
spool off
EXIT
I hope it helps!
---------- Post updated at 15:15 ---------- Previous update was at 15:12 ----------
I forgot two things:
- To validate the log file inside the while loop:
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
if [ $chk_err -ne 0 ]
then
break
fi
- Add the sleep inside the loop:
sleep ${loopSleepTime}
---------- Post updated at 15:17 ---------- Previous update was at 15:15 ----------
I understand your script little bit as i am new to scripting i am unable to understand some part of the script. If possible can you help me in understanding the script.
pid=`ps -ef | egrep "${sqlFile}" | awk '{print $2}'`
chk_err=`egrep -i 'ORA-|SP2-|TNS-|Usage' "${sqlplusOutputLog}" | wc -l`
and how can we get the myPID=$$ .
Thanks for your quick reply. Can you help me how to learn shell scripting. If you have any good docs for shell scripting so that starters can easily learn .
I'm new to utilities like socat and netcat and I'm not clear if they will do what I need.
I have a "compileDeployStartWebServer.sh" script and a "StartBrowser.sh" script that are started by emacs/elisp at the same time in two different processes.
I'm using Cygwin bash on Windows 10.
My... (3 Replies)
Hello all,
I am facing a weird issue while executing a code below -
#!/bin/bash
cd /wload/baot/home/baotasa0/sandboxes_finance/ext_ukba_bde/pset
sh UKBA_publish.sh UKBA 28082015 3
if
then
echo "Param file conversion for all the areas are completed, please check in your home directory"... (2 Replies)
I am trying to call a script(callingscript.sh) from a master script(masterscript.sh) to get string type value from calling script to master script. I have used scripts mentioned below.
#masterscript.sh
./callingscript.sh
echo $fileExist
#callingscript.sh
echo "The script is called"... (2 Replies)
I am using blow script :--
#!/bin/bash
FIND=$(ps -elf | grep "snmp_trap.sh" | grep -v grep) #check snmp_trap.sh is running or not
if
then
# echo "process found"
exit 0;
else
echo "process not found"
exec /home/Ketan_r /snmp_trap.sh 2>&1 & disown -h ... (1 Reply)
Hi guys
I have a shell script that executes sql statemets and sends the output to a file.the script takes in parameters executes sql and sends the result to an output file.
#!/bin/sh
echo " $2 $3 $4 $5 $6 $7
isql -w400 -U$2 -S$5 -P$3 << xxx
use $4
go
print"**Changes to the table... (0 Replies)