Deux petits scripts permettant le recalcul des index d’une base oracle lorsque ceux-ci ont l’air louche … Les scripts sont à passer l’un après l’autre sur la base

--- AnalyseIndex.sql
set pages 9999;
set heading off;
set feedback off;
set echo off;
set linesize 255;

spool step1.sql;
select 'drop table system.temp_stats_paul;' from dual;
select 'create table system.temp_stats_paul as select name, most_repeated_key, distinct_keys, del_lf_rows, height, blks_gets_per_access, lf_rows from index_stats;' from dual;

select 'analyze index '||owner||'.'||index_name||' validate structure;',
'insert into system.temp_stats_paul ( select name, most_repeated_key, distinct_keys, del_lf_rows, height, blks_gets_per_access, lf_rows from index_stats where height > 3 or ( 100 * del_lf_rows / (lf_rows+1) ) > 20 or BLKS_GETS_PER_ACCESS > 5 );'
from dba_indexes
where
owner not in ('SYS','SYSTEM');

spool off;
set heading on;
set feedback on;
set echo on;

@step1.sql
quit

--- RebuildIndex.sql
set pages 9999;
set heading off;
set feedback off;
set echo off;
set linesize 255;

spool step2.sql;

select 'alter index '||owner||'.'||name||' rebuild tablespace '||tablespace_name||';'
from system.temp_stats_paul, dba_indexes
where system.temp_stats_paul.name = dba_indexes.index_name;

spool off;

drop table system.temp_stats_paul;

set heading on;
set feedback on;
set echo on;

@step2.sql
quit