Index Rebuild -> alle unusable Idx

Wenn ein Index "kaputt" geht, geht bisweilen nicht mehr viel mit der zugehörigen Tabelle.

Zum Prüfen, ob Indizes 'unbenutzbar' sind:
(Wichtig ist die getrennte Prüfung für Index, Partition und Subpartition, daher der union all; die Spalten partition_name und ddl_type werden später zur Generierung des SQL benötigt.)
select owner
     , index_name
     , null partition_name
     , null ddl_type
  from all_indexes
 where table_owner = UPPER ('IPUCT3')
   and status = 'UNUSABLE'
union all
select index_owner
     , index_name
     , partition_name
     , 'partition' ddl_type
  from all_ind_partitions
 where     (index_owner, index_name) in (select owner, index_name
                                           from all_indexes
                                          where table_owner = UPPER ('IPUCT3'))
   and status = 'UNUSABLE'
union all
select index_owner
     , index_name
     , subpartition_name
     , 'subpartition' ddl_type
  from all_ind_subpartitions
 where     (index_owner, index_name) in (select owner, index_name
                                           from all_indexes
                                          where table_owner = UPPER ('IPUCT3'))
   and status = 'UNUSABLE'
;

Komplettes "Reparieren":

procedure Rebuild_unusable_Idx        -- Purpose : Kaputte Indizes in AMM16_STAGE und AMM16 wiederherstellen
is
  v_ErrCode varchar2(100);       -- Fehlercode
  v_ErrTxt  varchar2(1500);      -- Fehlermeldung
 
  c_Modul   varchar2(100) := 'Rebuild_unusable_Idx';
 
  v_Sql     varchar2(4000);  -- SQL-String

begin
 
  log(c_Modul, 'BEGIN Rebuild_unusable_Idx');
 
  for i in (
    select index_owner, index_name, partition_name, 'partition' ddl_type
        from all_ind_partitions
        where (index_owner,index_name) in
           ( select owner, index_name
             from   all_indexes
             where table_owner in (upper('mySchema1'), upper('mySchema2'))
           )
        and status = 'UNUSABLE'
    union all
    select owner, index_name, null, null
        from all_indexes
        where table_owner in (upper('mySchema1'), upper('mySchema2'))
        and status = 'UNUSABLE'   
    -- hier ohne Subpartitions, da es keine gibt (FS, 31.07.2019)
  ) loop
    if i.ddl_type is null then
      -- a) gesamten Index wiederherstellen
      v_Sql := 'alter index '||i.index_owner||'.'||i.index_name||' rebuild';
      log(c_Modul, v_Sql);
      if i.index_owner = 'mySchema2'
        then mySchema2.part_util.Run_Execute_Immediate(v_Sql);
        else execute immediate v_Sql;
      end if;     
      log(c_Modul, 'Index-Rebuild erfolgreich');
    else
      -- b) Partition wiederherstellen
      v_Sql := 'alter index '||i.index_owner||'.'||i.index_name||' rebuild '||i.ddl_type||' '||i.partition_name;
      log(c_Modul, v_Sql);
      if i.index_owner = 'mySchema2'
        then mySchema2.part_util.Run_Execute_Immediate(v_Sql);
        else execute immediate v_Sql;
      end if;     
      log(c_Modul, 'Index-Rebuild erfolgreich');
    end if;
  end loop;
 
  log(c_Modul, 'ENDE Rebuild_unusable_Idx');

exception
  when others then
    v_ErrCode := sqlcode;
    v_ErrTxt  := substr(sqlerrm,1,300);
    log(c_Modul, 'FEHLER Rebuild_unusable_Idx '||v_ErrCode||' / '||v_ErrTxt);
    raise;
end Rebuild_unusable_Idx;




Quelle: https://asktom.oracle.com/pls/asktom/asktom.search?tag=rebuilding-all-the-unusable-index