Project Verify Issue: The current text search feature is not configured well

1) Login to Oracle as CTXSYS and issue this query: modified_drdefus.sql

2) If not already granted, grant CTXAPP role as default. This can be done by issuing the following queries to the Oracle schema ID...

GRANT "CTXAPP" TO "<schema_id" ;
ALTER USER "<schema_id" DEFAULT ROLE "CTXAPP";

For example...

GRANT "CTXAPP" TO "CUSTOMER_TEXTSEARCH_DB" ;
ALTER USER "CUSTOMER_TEXTSEARCH_DB" DEFAULT ROLE "CTXAPP";

3) Drop Text Search indexes on the project schema in Oracle by issuing the following...

DROP INDEX BG_TS_IX;
DROP INDEX BPME_TS_IX;
DROP INDEX BPMM_TS_IX;
DROP INDEX BPMP_TS_IX;
DROP INDEX CO_TS_IX;
DROP INDEX CS_TS_IX;
DROP INDEX DS_TS_IX;
DROP INDEX RN_TS_IX;
DROP INDEX RQ_TS_IX;
DROP INDEX TS_TS_IX;
commit;

3a) Optionally you can parameterize the "DROP INDEX" queries by populating the schema namd in the "<scheme_name>" using the following.

define p1_project_user_schema = <schema_name>;
DROP INDEX &&p1_project_user_schema .BG_TS_IX;
DROP INDEX &&p1_project_user_schema .BPME_TS_IX;
DROP INDEX &&p1_project_user_schema .BPMM_TS_IX;
DROP INDEX &&p1_project_user_schema .BPMP_TS_IX;
DROP INDEX &&p1_project_user_schema .CO_TS_IX;
DROP INDEX &&p1_project_user_schema .CS_TS_IX;
DROP INDEX &&p1_project_user_schema .DS_TS_IX;
DROP INDEX &&p1_project_user_schema .RN_TS_IX;
DROP INDEX &&p1_project_user_schema .RQ_TS_IX;
DROP INDEX &&p1_project_user_schema .TS_TS_IX;
commit;

4) In the site admin gui, for the project in question, click the 'Enable/Rebuild Text Search'

5) Check to make sure the list of indexes listed in in Step 3, (optionally 3a) are created in Step 4 by issuing the following query...

select * from dba_indexes where ityp_owner = 'CTXSYS' and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH') and owner = '<schema_name>' order by index_name;

For example...

select * from dba_indexes where ityp_owner = 'CTXSYS' and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH') and owner = 'DEFAULT_ALMDEMO_DB' order by index_name;

The project should now pass Verify for Text Search functionality