Being Master of RDBMS world. We must aware of Index on the database. Indexes play an important role in the life of Database management.  Today we are going to have look at the steps which we use to “Fix Invalid Or Unusable Index“.

Let’s have look at the steps.

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')
select index_name name,partition_name partition,'No Subpartition' Subpartition,status from all_ind_partitions where status not in('VALID','USABLE','N/A')
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 a 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');


— As Scott user

SQL> insert into scott.dept_1(deptno_1,dname_1,loc_1) values(50,'Test','Test');

insert into scott.dept_1(deptno_1,dname_1,loc_1) values(50,'Test','Test')
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPT_1' or partition of such index is in unusable State

Check all the index that are unusable state

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

alter index SCOTT.PK_DEPT_1 rebuild online;

alter index SCOTT.PK_EMP_1 rebuild online;

Rebuilding the Index

SQL> alter index SCOTT.PK_DEPT_1 rebuild online;

Index altered.

SQL> alter index SCOTT.PK_EMP_1rebuild online;

Index altered.

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

no rows selected

Rerun the insert now.

SQL> insert into scott.dept_1(deptno_1,dname_1,loc_1) values(50,'Test','Test');
1 row created.

SQL> commit;

Commit complete.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel:

Skant Gupta’s LinkedIn:

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.