Gather and schedule Oracle table statistics via the operating system crontab
#!/bin/bash
export ORACLE_SID=oracledbwr
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/19c/db_1
PATH=$ORACLE_HOME/bin:$PATH
export PATH
sqlplus -s / as sysdba <<EOF
set echo on
set heading on
set feedback on
set timing on
spool /home/oracle/scripts/gather_stats.log
select to_char(sysdate,’dd-mm-yyyy hh24:mi:ss’) from dual;
exec dbms_stats.gather_table_stats(‘HR’,tabname=>’EMP’,ESTIMATE_PERCENT=>30,granularity=>’ALL’,CASCADE=>True,degree=>4);
spool off
exit;
EOF
mailx -s “EMPLOYEE Table analyzed” -r oracledbwr@gmail.com </home/oracle/scripts/gather_stats.log
