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.