Script to analyze all table and index partitions individually

set feed off echo off head off trimspool on line 500

spool /tmp/analyze$$.sql

select 'ANALYZE TABLE '||table_owner||'.'||table_name||' partition ('||
       partition_name||') estimate statistics;'
from   sys.dba_tab_partitions
/

select 'ANALYZE INDEX '||index_owner||'.'||index_name||' partition ('||
       partition_name||') estimate statistics;'
from   sys.dba_ind_partitions
/

spool off

set feed on
@/tmp/analyze$$.sql
! rm /tmp/analyze$$.sql
  • March 10, 2019 | 27 views
  • Comments