Index Unusable Oracle | How to Find and Fix Easily

Oracle databases can have invalid indexes that are not usable. Such index unusable oracle state is UNUSABLE. Oracle DBA_INDEXES view returns all the indexes that are available in the database. Indexes are become unusable for various reasons. Such a situation can happen once partition maintenance operations, DDL changes, or errors during index creation or rebuilding.

Unusable indexes are not usable until re-build the index or need to be removed or recreated to use them again. Otherwise, can raise performance problems in the database due to an invalid index. We expect queries retrieved through the index but it is not in use.

Create an index correctly and check Oracle errors for build problems. Indexes have a huge impact on performance because of index can fetch data from databases. Order dataset (Order by Oracle), and many cases. Let’s see how to get the index unusable in Oracle.

Check Unusable Indexes:

• Query for  DBA_INDEXES view to check the status of indexes:

--
SELECT INDEX_NAME, TABLE_NAME, STATUS 
FROM DBA_INDEXES 
WHERE STATUS = 'UNUSABLE'; 
--

• The query for dba_ind_PARTITIONS view to check the status of indexes Partitions:

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

• The query for dba_ind_SUBPARTITIONS view to check the status of index subpartitions:

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

• If you have an error like below you may have a problem with the index being unusable in Oracle

--
'ORA-01502: index `…` or partition of such index is in unusable state' 
--

Why indexes become unusable

Indexes have become invalidated due to the below reasons. Some DB tasks like those below can invalidate indexes.

• Table partition maintenance – When doing the maintenance of table partitions like move, split, or truncate partitions will shift ROWIDs, making the index invalid and unusable.
• Someone execute ALTER INDEX <index name> UNUSABLE;
   
• CTAS maintenance – Table reorganization with “alter table move” or an online table reorganization (using the dbms_redefinition package) will shift ROWIDs, creating unusable indexes.
   
• Oracle imports – If Oracle database import using skip_unusable_indexes=y parameter then the index unusable in Oracle
   
• SQL*Loader (sqlldr utility) – Using direct path loads like skip_index_maintenance indexes can be invalidated.

Fixing Unusable Indexes:

1. Rebuilding the Index: Rebuild the index by using the ALTER INDEX statement:

--
ALTER INDEX index_name REBUILD; 
--

Replace index_name with the name of the unusable index.

2. Dropping and Recreating the Index: A simple way is you can easily remove the index and create the index correctly again

--Drop index
DROP INDEX index_name; 
--Create index
CREATE INDEX index_name ON table_name (columns); 
--

Replace index_name, table_name, and columns according to your required values

3. Automatic Rebuilding:

index unusable oracle but you have the option to rebuild automatically. It is supported on the Enterprise Edition of Oracle, you just need to enable the ENABLE_BLOCK_RECOVERY setting parameter. This parameter automatically rebuilds unusable indexes.

--
ALTER SYSTEM SET ENABLE_BLOCK_RECOVERY = TRUE; 
--

You must test this option in a test environment first and use it in the production system.

The query for altering invalid indexes

--
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||' ONLINE PARALLEL;' sql_to_rebuild_index
FROM   dba_indexes
WHERE  status = 'UNUSABLE';
--

How to Prevent Index Unusable Oracle

Some best practices are used to prevent unusable indexes in Oracle. Below are some of them:

• Some DDL operations like table truncation can be index invalidated therefore careful index maintenance in such an operation.
• Regularly check and confirm unusable indexes using the above queries.
• Mindful of index creation or maintenance operations whether there are any errors.

Always follow best practices on database work, especially on critical DML operations or maintenance work. Taking backups, being mindful of errors, logging systems, and Online alerting are some of them. If indexes are unusable Performance can be dropped.  

https://www.dba-oracle.com/t_indexes_invalid_unusable.htm