Alle Indizes finden, die neu aufgebaut werden müssen.

SET SERVEROUTPUT ON
DECLARE
 V_OWNER VARCHAR2(30) :='SYSMAN'; --Hier zu analysierendes Schema angeben
 V_SHOW_REBUILD_INDEXES_ONLY BOOLEAN NOT NULL:=FALSE;
 V_PCT_DEL_ROWS NUMBER(2) NOT NULL:=30;
 V_INDEX_MAX_SIZE_MB NUMBER(5,2) NOT NULL:=1;
 REC_INDEXSTATS INDEX_STATS%ROWTYPE;
 CURSOR C_INDEXES_FOR_REBUILD
 IS
  SELECT DISTINCT
   DI.OWNER,
   DI.INDEX_NAME
  FROM
   DBA_INDEXES DI
    INNER JOIN DBA_SEGMENTS DS
     ON DI.OWNER=DS.OWNER AND DI.INDEX_NAME=DS.SEGMENT_NAME
  WHERE
   DI.OWNER =V_OWNER AND
   DI.INDEX_TYPE LIKE '%NORMAL%' AND
   ((DS.BYTES/1024)/1024)>=V_INDEX_MAX_SIZE_MB;
 
BEGIN
 
 --Alle Objekte ausgeben, die zu analysieren sind
 DBMS_OUTPUT.PUT_LINE('REBUILD INDEXES:');
 DBMS_OUTPUT.PUT_LINE('=======================');
 FOR REC_INDEXES_FOR_REBUILD IN C_INDEXES_FOR_REBUILD
 LOOP
  DECLARE
   EX_LOCKED_INDEX EXCEPTION;
   PRAGMA EXCEPTION_INIT(EX_LOCKED_INDEX, -04022);
  BEGIN
   EXECUTE IMMEDIATE 'ANALYZE INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
              REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' VALIDATE STRUCTURE';
   SELECT
    *
   INTO
    REC_INDEXSTATS
   FROM
    INDEX_STATS;
   IF (REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100 >=V_PCT_DEL_ROWS
   THEN
     IF V_SHOW_REBUILD_INDEXES_ONLY=FALSE
     THEN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
                REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' REBUILD ONLINE';
     END IF;
     DBMS_OUTPUT.PUT_LINE(ROUND((REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100,2) || ' ' ||
           REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
           REC_INDEXES_FOR_REBUILD.INDEX_NAME);
   END IF;
  EXCEPTION
   WHEN EX_LOCKED_INDEX
   THEN
    DBMS_OUTPUT.PUT_LINE('INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
             REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' COULD NOT BE RBUILD. DDL TIMOEOUT');
  END;
 END LOOP;
END;
/