ORA-30554: function-based index is disabled

Problem

Following error occured from DB when access the table

Rejected - Error on table GPS.MAIN_PROD.
ORA-30554: function-based index GPS.FN_IDX_MAIN_PROD_1 is disabled

Solution

Check the table and index status

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'MAIN_PROD';

OBJECT_NAME  OBJECT_TYPE  OWNER   STATUS
-----------  -----------  ------- ---------
MAIN_PROD      TABLE       GPS     VALID

SQL> select object_name,object_type,owner,status from dba_objects where object_name like 'FN_IDX_MAIN_PROD_1';

OBJECT_NAME         OBJECT_TYPE  OWNER  STATUS
------------       ------------ ------ -------
FN_IDX_MAIN_PROD_1    INDEX      GPS     VALID

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     DISABLED

Re-build index which was disabled

SQL> alter index gps.FN_IDX_MAIN_PROD_1 rebuild;

Index altered.

Check the index status again confirm whether issue is sorted

SQL> select owner, index_name from dba_indexes,funcidx_status where index_name like 'FN_IDX_MAIN_PROD_1';

OWNER       INDEX_NAME      STATUS   FUNCIDX_STATUS
------ ------------------- --------  --------------
 GPS    FN_IDX_MAIN_PROD_1   VALID     ENABLED