How to rebuild the unusable index in oracle

In this post, we will learn Why index become unusable in Oracle, query to check unusable indexes in oracle, How to rebuild the unusable index in oracle,how to rebuild the unusable index partition in oracle

Why index become unusable 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 Maintainance 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 subpartitions in an unusable state. Let’s check the queries to find and rebuild them

Indexes:

SELECT owner, index_name, tablespace_name
FROM dba_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

We can generate the rebuild command like this

SQL>set pages 0
SQL> spool index_rebuild.sql
SQL> select 'alter index '||index_name||' rebuild;' from dba_indexes where status ='UNUSABLE';
SQL> spool off
SQL>@ index_rebuild.sql 

We can check if we still have an unusable index after the above rebuild completion

 SELECT owner, index_name, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE'; 

How to rebuild the unusable index partition in oracle

We can generate the rebuild command like this

SQL>set pages 0
SQL> spool index_part_rebuild.sql
SQL> select ''alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name||' ;' from dba_ind_partitions where status ='UNUSABLE';
SQL> spool off
SQL>@index_part_rebuild.sql 

We can check if we still have an unusable index after the above rebuild completion

SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';

How to rebuild the Index sub-Partition

SQL>set pages 0
SQL> spool index_partsub_rebuild.sql
SQL> select 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||' ;'
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE';
SQL> spool off
SQL>@index_partsub_rebuild.sql

We can check if we still have an unusable index after the above rebuild completion

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE';

We can use online, parallel clauses accordingly as per your convenience

We can also club them together with the below SQL

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

Hope you like this post on Why indexes become unusable in Oracle, query to check unusable indexes in oracle, How to make unusable index to valid in Oracle,how to rebuild the unusable index partition in oracle. Please do provide feedback

Related Articles

How to Drop the index in Oracle : check out this post to learn about How to Drop the index in Oracle and how to drop the unique index in oracle
find indexes on a table in oracle : check out this article to find queries on how to find indexes on a table in oracle,list all indexes in schema,index status, index column
types of indexes in oracle with example : This page consist of oracle indexes information,different types of indexes in oracle with example,how to create/drop/alter the index in oracle