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

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


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google