Find Invalid,Unusable Indexes.Rebuild Index.

Check the Status of Indexes:

SELECT status,count(*) from USER_INDEXES GROUP BY STATUS;

SELECT status,count(*) from ALL_INDEXES GROUP BY STATUS;

SELECT status,count(*) from DBA_INDEXES GROUP BY STATUS;

Solution

   1. Drop the specified index and/or recreate the index

   2. Rebuild the specified index

   3. Rebuild the unusable index partition

Rebuilding UNUSABLE indexes online, querying to USER_INDEXES,ALL_INDEXES,DBA_INDEXES view.

Sql>  SELECT count(*),status FROM all_indexes GROUP BY status;

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

Output :

 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index APPLSYS.FND_COLUMNS_U4 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_PK rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N1 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U3 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U1 rebuild online ;