Script to rebuild all UNUSABLE indexes in oracle

Index rebuild has to be done in oracle databases for different reasons. A planned index rebuild activity can be done monthly or ewe weekly on all indexes that has a benefit after rebuild.

Read Oracle support note on how to find the candidate index to rebuild for performance benefit.

Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)

Another immediate index rebuild is to be done when there are unusable indexes in the database.
Oracle index can become unusable for many reasons. Presence of unusable index will effect DML operations in a large extend. Unusable indexes should be rebuilt immediately they are discovered.
(Read: How does an oracle index become unusable)

If there are only a few indexes unusable, We can rebuild the unusable indexes one by one manually. But is there are a lot of indexes to be rebuild, manually rebuilding each index one by one will be a time consuming activity. Also it will take longer if there are partitioned and sub-partitioned indexes.

The script given below will rebuild all indexes selected by the query
   SELECT OWNER, INDEX_NAME,  INDEX_TYPE, PARTITIONED
   FROM ALL_INDEXES 
   WHERE  STATUS = 'UNUSABLE' 
   ORDER BY 1,4;

The WHERE condition can be changed to change the candidate index set. For example if you want to rebuild all indexes from a particular schema you can give condition on the column OWNER.

Find the pl/sql script below just copy paste it to rebuild all unusable indexes including index partitions and index subpartitions in your oracle database.



SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting: UNUSABLE index rebuild');
DBMS_OUTPUT.PUT_LINE('UNPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
            SELECT OWNER,INDEX_NAME FROM ALL_INDEXES 
            WHERE STATUS='UNUSABLE' AND PARTITIONED='NO' ORDER BY 1,2
    )
    LOOP
        BEGIN
              EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER ||'.'|| I.INDEX_NAME || ' REBUILD' ;
              DBMS_OUTPUT.PUT_LINE('REBUILT INDEX '|| I.OWNER ||'.'|| I.INDEX_NAME );
        EXCEPTION
              WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING INDEX: '|| I.OWNER ||'.'|| I.INDEX_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('PARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM ALL_IND_PARTITIONS
             WHERE STATUS='UNUSABLE' AND SUBPARTITION_COUNT=0 ORDER BY 1,2,3
                        )
    LOOP
        BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD PARTITION ' || I.PARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' PARTITION ' || I.PARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING PARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.PARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('SUBPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM ALL_IND_SUBPARTITIONS
             WHERE  STATUS='UNUSABLE' ORDER BY 1,2,3
     )
     LOOP
         BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD SUBPARTITION ' || I.SUBPARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '||  I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' SUBPARTITION ' || I.SUBPARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING SUBPARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.SUBPARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('END: UNUSABLE index rebuild');               
END;
/

Here in the script only basic offline rebuild is included. Other clauses like ONLINE, PARALLEL, NOLOGGING etc can be given as per your requirement.