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.
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.