HOW TO REBUILD THE UNUSABLE INDEX IN ORACLE

Great things never came from comfort zones.

HELLO FRIENDS,

IN THIS POST WE WILL LEARN, ABOUT A SIMPLE AND IMPORTANT TOPIC.

WHY INDEX BECOMES UNUSABLE IN ORACLE, HOW TO REBUILD THE INDEXES IN ORACLE, HOW TO REBUILD THE UNUSABLE INDEX PARTITION IN ORACLE.

CAUSES OF BECOMING UNUSABLE INDEX IN ORACLE.

  • SOMETIMES WHEN YOU ARE REBUILDING THE TABLE AND YOU FORGET TO REBUILD THE INDEX AFTER THAT, THEN THE INDEX OF THE TABLE WILL BE IN UNUSABLE STATUS
  • THE INDEX CAN ALSO BECOME UNUSABLE WITH THE SHRINK TABLE COMMAND
  • PARTITION MAINTENANCE OPERATIONS LIKE SPLIT, MOVE, TRUNCATE CAN ALSO RENDER INDEX UNUSABLE.

QUERY TO CHECK UNUSABLE INDEXES IN ORACLE:

WE CAN HAVE AN INDEX, INDEX PARTITION, AND INDEX SUB PARTITIONS IN AN UNUSABLE STATE. LET’S CHECK THE QUERIES TO FIND AND REBUILD THEM,

SELECT OWNER, INDEX_NAME  

FROM DBA_INDEXES

WHERE STATUS = ‘UNUSABLE’;

OR

SELECT TABLE_OWNER, INDEX_NAME

  FROM USER_INDEXES                    

 WHERE STATUS = ‘UNUSABLE’;

Index partitions:

SELECT

           index_owner,

           index_name,

           partition_name,

           tablespace_name

FROM dba_ind_PARTITIONS

WHERE status = ‘UNUSABLE’;

Index subpartitions:

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name

FROM dba_ind_SUBPARTITIONS

WHERE status = ‘UNUSABLE’;

How to rebuild the unusable index in oracle:

Alter index TABLE_OWNER.INDEX_NAME rebuild;

For to rebuild all the indexes which are in unusable status I have created a dynamic procedure to make all valid in a single statement.

BEGIN

    FOR I IN (

                SELECT TABLE_OWNER, INDEX_NAME         

                  FROM USER_INDEXES                    

                 WHERE STATUS = ‘UNUSABLE’

             )

    LOOP

        EXECUTE IMMEDIATE

            ‘ALTER INDEX ‘ || I.TABLE_OWNER || ‘.’ || I.INDEX_NAME || ‘ REBUILD’;

             DBMS_OUTPUT.PUT_LINE(‘INDEX REBUILD SUCCESSFULLY COMPLETED’);

             commit;

    END LOOP;

    EXCEPTION

               WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE(‘EXCEPTION OCCURRED. DETAILS –> ‘ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    DBMS_OUTPUT.PUT_LINE(‘SQL Error Code : ‘ || SQLCODE || ‘Error Msg : ‘ ||  SQLERRM);

END;

/

You may also like...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: