How to Automate / Schedule Oracle Gather Table Statistics Job through Crontab?

advertisements

_____________________________________________________________________________________________________________________

Step 1: Create a shell script to accept a sql file and execute against a particular database. Make necessary changes to the ORACLE_SID, ORACLE_BASE, ORACLE_HOME parameters.

$ vi sql.sh

export ORACLE_SID=transprd

export ORACLE_BASE=/u00/app/oracle

export ORACLE_HOME=/u00/app/oracle/product/11.2.0.3/db_1

export

PATH=/u00/app/oracle/product/11.2.0.3/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u00/app/oracle/product/11.2.0.3/db_1/bin:.

sqlplus sthomas/voyage123 << EOF > /home/oracle/st/gath.log

@$1

exit

EOF

Step 2: Change the permission for the shell script

$ chmod 744 sql.sh

Step 3: Create a sql script file to generate the scripts for gather statistics command. Make necessary changes to the table names mentioned in red color below.

$ cat cr_gather.sql

set pages 100

set lines 250

set echo off feedback off heading off

spool gather.sql

select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;

select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||', degree => 8);' from   dba_tables

where table_name in ('EMP', 'DEPT');

spool off

Step 4: Run the shell script with argument as the sql script file (cr_gather.sql). This will create a gather.sql script file in the same directory.

$ sql.sh cr_gather.sql

Step 5: Add a crontab entry using crontab –e command with required execution timings.

01 23 * * * /home/oracle/st/sql.sh /home/oracle/st/gather.sql > /home/oracle/st/cron.log

_____________________________________________________________________________________________________________________

Website Stats