Linux

 if [[ $(grep -E "ORA-|ERROR" /tmp/schema.log ) ]]
then
mailx -s "USER Stats gather succeed on instance xyz on host $a" $PSDBA < /tmp/schema.log
else
mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA < /tmp/schema.log
exit 0
fi

[ $(grep -E "ORA-|ERROR" /tmp/schema.log ) ] || ( mailx -s "USER Stats gather failed on instance xyz on host $a" $PSDBA ) < /tmp/schema.log

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

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

kill -15 ${pid}
kill -9 ${pid}

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=$$ .