AR.HZ_LOCATIONS_N15 is UNUSABLE

Problem;
*******
AR.HZ_LOCATIONS_N15 is UNUSABLE .

SQL> select index_name,owner,status from dba_indexes where status='UNUSABLE';

INDEX_NAME OWNER STATUS
------------------------------ ------------------------------ --------
HZ_LOCATIONS_N15 AR UNUSABLE


Solution
********
a) delete user_sdo_geom_metadata
where table_name='HZ_LOCATIONS'
and COLUMN_NAME='GEOMETRY';

b) drop index AR.HZ_LOCATIONS_N15 force;

c) cd $AR_TOP/patch/115/sql
sqlplus ar/ar @ARHGEOIS.sql
sqlplus apps/apps @ARHGEOID.sql

You'll be prompted to enter a value, choose AR
Enter the value for value 1 : AR


Verify the Object by running the below query as APPS.

select index_name,status from dba_indexes where table_name='HZ_LOCATIONS' and index_name='HZ_LOCATIONS_N15';

Rgds,
Madan