Saturday, March 1, 2014

How to generate analyze statements for Oracle

Sometimes you get stuck with DBAs that can't be bothered to analyze your tables or indexes. There is an easy way to generate your own analyze statements. This example is assuming you're using sqlplus. Remove <TABLE_OWNER> with you're own table owner.

set pagesize 0
set linesize 255
spool /tmp/analyze-table.sql

select 'analyze table <TABLE_OWNER>.' || object_name || ' compute statistics;' from all_objects where owner like '<TABLE_OWNER>%' and object_type = 'TABLE';
exit

You will now have all the statements you need in /tmp/analyze-table.sql