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;
/
