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.