Fix Invalid Or Unusable Index in Oracle

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')
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 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');

Example:

— 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';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
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: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp