Skip to content
Home » Oracle Database » Schema Object » Index » How to Resolve Some indexes or index [sub]partitions of table have been marked unusable

How to Resolve Some indexes or index [sub]partitions of table have been marked unusable

Some indexes or index [sub]partitions of table have been marked unusable

Saw errors in the alert log file, which indicates that some indexes depends on the table have become unusable.

...
ORCLPDB(3):Some indexes or index [sub]partitions of table ERPAPP.PAY_HIST have been marked unusable

The error means that some indexes the optimizer wants to use are UNUSABLE, alternative paths or scanning full table may be chosen as the execution plan.

Becoming UNUSABLE of index usually happens after you dropped some table partitions they depend, or someone intentionally make it UNUSABLE. No matter which condition you're in, the solution is to rebuild it. Let's see how we solve the problem.

Solution

To know which indexes become unusable, we query ALL_INDEXES like this:

SQL> column owner format a20;
SQL> column index_name format a20;
SQL> select owner, index_name from all_indexes where table_owner = 'ERPAPP' and table_name = 'PAY_HIST' and status = 'UNUSABLE';

OWNER                INDEX_NAME
-------------------- --------------------
ERPAPP               IDX10
ERPAPP               IDX11

To solve the problem, we should rebuild those unusable indexes, just like we did to solve ORA-01502. To compose some handy statements, we do it like this:

SQL> select 'ALTER INDEX "' || owner || '"."' || index_name || '" REBUILD TABLESPACE ERPAPP_TS_08 ONLINE;' stmt from all_indexes where table_owner = 'ERPAPP' and table_name = 'PAY_HIST' and status = 'UNUSABLE';

STMT
--------------------------------------------------------------------------------
ALTER INDEX "ERPAPP"."IDX10" REBUILD TABLESPACE ERPAPP_TS_08 ONLINE PARALLEL 16;
ALTER INDEX "ERPAPP"."IDX11" REBUILD TABLESPACE ERPAPP_TS_08 ONLINE PARALLEL 16;

In the above statement, we rebuild the index to another tablespace online and parallelly.

Let's check the result.

SQL> select index_name, status from all_indexes where owner = 'ERPAPP' and index_name in ('IDX10', 'IDX11');

INDEX_NAME           STATUS
-------------------- --------
IDX10                VALID
IDX11                VALID

The problem has been fixed.

Parallelly rebuilding an index affects the degree of parallelism (DOP), you should be careful.

Leave a Reply

Your email address will not be published. Required fields are marked *