Back to Scripting | Analyze Tables and Indexes


This job runs a script which generates the necessary "ANALYZE TABLE" DDL statements by reading the data dictionary and spooling the results to a file, then runs the spooled file just generated. The generation script can be modified and tailored to your particular needs.


Analyze.cmd

logevent -s I "Oracle ANALYZE job started"

REM  This script generates the ANALYZE script that will be run by reading the 
REM  Data Dictionary, concatenating table names with the ANALYZE statement
REM  commands, then spooling the output to a text file, then it runs the generated
REM  script.
plus80 system/manager@db1.world @d:\YourPath\Analyze.sql

logevent -s I "Oracle ANALYZE job finished"
exit

Analyze.sql

SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 200
SET PAGESIZE 0
SPOOL D:\YourPath\temp.sql
SELECT 'ANALYZE TABLE '||owner||'.'||table_name||' COMPUTE STATISTICS '||
       'FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 75;'
        FROM dba_tables
        WHERE owner NOT IN ('SYS','SYSTEM')
        ORDER BY owner, table_name;
SPOOL OFF
SET ECHO ON
SET FEEDBACK ON
SPOOL D:\YourPath\Analyze.log
@D:\YourPath\temp.sql;
SPOOL OFF
EXIT

Adding this job to the scheduler

AT 5:00AM /EVERY:M,T,W,Th,F,S,Su "D:\YourPath\Analyze.cmd"