Fix Invalid Or Unusable Index

Check Unusable or Invalid Index:

select index_name name,'No Partition' partition,'No Subpartition' Subpartition,status from all_indexes where status not in('VALID','USABLE','N/A')

union

select index_name name,partition_name partition,'No Subpartition' Subpartition,status from all_ind_partitions where status not in('VALID','USABLE','N/A')

union

select index_name name,partition_name partition,subpartition_name Subpartition,status from all_ind_subpartitions where status not in('VALID','USABLE','N/A');

Rebuild Unusable or Invalid Index:

-- If the index does not have any partition

select 'alter index '||owner||'.'||index_name||' rebuild online;' from all_indexes where status not in('VALID','USABLE','N/A');

-- If the index is partition

Select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' ONLINE;' from all_ind_partitions where status not in('VALID','USABLE','N/A');

-- If the index is Subpartition

Select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' ONLINE;' from all_ind_subpartitions where status not in('VALID','USABLE','N/A');

Example:

-- As scott user

SQL> insert into scott.dept(deptno,dname,loc) values(50,'Test','Test');

insert into scott.dept(deptno,dname,loc) values(50,'Test','Test')

*

ERROR at line 1:

ORA-01502: index 'SCOTT.PK_DEPT' or partition of such index is in unusable State

-- As sys user

-- Check all the index that are unusable state

SQL> select 'alter index '||owner||'.'||index_name||' rebuild online;' from all_indexes where status ='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'

--------------------------------------------------------------------------------

alter index SCOTT.PK_DEPT rebuild online;

alter index SCOTT.PK_EMP rebuild online;

SQL> alter index SCOTT.PK_DEPT rebuild online;

Index altered.

SQL> alter index SCOTT.PK_EMP rebuild online;

Index altered.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild online;' from all_indexes where status ='UNUSABLE';

no rows selected

SQL> insert into scott.dept(deptno,dname,loc) values(50,'Test','Test');

1 row created.

SQL> commit;

Commit complete.