Crontab - Stats gathering schema status on mail

Crontab - Stats gathering schema status on mail

Crontab Details :

0 4 * * 0 /u04/MasterDB/scripts/PRODB_statsgather.sh

Scripts Details:

oracle@test02:~> cat /u04/MasterDB/scripts/PRODB_statsgather.sh

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export ORACLE_SID=PRODB

export PATH=$PATH:$ORACLE_HOME/bin

stats_log=/u04/MasterDB/scripts/dmenvstats.log

printf "to:varun.yadav@abc.com\n" > $stats_log

printf "cc:abc@abc.com\n" >> $stats_log

printf "subject:Production test02 Schema stats gather details " >> $stats_log

sqlplus -s "/as sysdba" <<EOF >> $stats_log

set lines 200

PROMPT DATABASE NAME

PROMPT =================

Select name,open_mode,database_role,db_unique_name from v\$database;

PROMPT test02 Schema stats gather details

PROMPT ==================================================

exec DBMS_Stats.Gather_Schema_Stats('test02', estimate_percent => 10, degree => 3,cascade => true);

EOF

/usr/sbin/sendmail -t < $stats_log

OUTPUT:

=================

 

NAME     OPEN_MODE        DATABASE_ROLE DB_UNIQUE_NAME

--------- -------------------- ---------------- ------------------------------

TEST02            READ WRITE         PRIMARY               TEST02

 

TEST02 Schema stats gather details

==================================================

 

PL/SQL procedure successfully completed.