Saturday 26 October 2013

ORA-29871: invalid alter option for a domain index

ORA-29871 error while rebuilding the Domain Index:

SQL> alter index HR.PER_EMPDIR_PEOPLE_N1 rebuild TABLESPACE APPS_TS_TX_DATA;
alter index HR.PER_EMPDIR_PEOPLE_N1 rebuild TABLESPACE APPS_TS_TX_DATA
               *
ERROR at line 1:
ORA-29871: invalid alter option for a domain index


Solution:
1. DROP INDEX "HR"."PER_EMPDIR_PEOPLE_N1";

2. Commit;

After migration create index using below command

3. CREATE INDEX "HR"."PER_EMPDIR_PEOPLE_N1" ON "HR"."PER_EMPDIR_PEOPLE" ("PERSON_KEY") INDEXTYPE IS "CTXSYS"."CONTEXT" ctx_ddl.set_attribute(TABLESPACE APPS_TS_TX_IDX);

4. Commit and then run the query:
select * from dba_objects where object_name like 'PER_EMPDIR_PEOPLE_N1'

Check whether the status of the index is valid.