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.cmdlogevent -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" exitAnalyze.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 EXITAdding this job to the scheduler
AT 5:00AM /EVERY:M,T,W,Th,F,S,Su "D:\YourPath\Analyze.cmd"