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.
|