ORA-29861 "domain index is marked LOADING"

Dropping an Oracle text index generates the error:

ORA-29861 "domain index is marked LOADING"

Forcing to drop the text with alter index <index_name> force; generates the end-of-file communication error.



After searching several documents, this is what I did:

I checked the status of the text index using the following:

select index_name, status, domidx_status, domidx_opstatus
from user_indexes where index_name = '<index_name>';

select index_name, partition_name, status, domidx_opstatus
from user_ind_partitions where index_name = '<index_name>';

All of the partitions have INPROGRS status. So I logged in as ctxsys and executed the following:

exec ctx_adm.mark_failed('<index owner name>','<index name>');

Then I logged in as the index owner, and check the status of the index:

select index_name, partition_name, status from user_ind_partitions where index_name='<index_name>';

At this point, all the documents I've searched said that the status should say failed. However, in our case, it says USABLE. Because I know that there's a problem, I went ahead and ran the following as the index owner:

begin
ctx_output.start_log ('<log_file_name>');
end;
/

alter index <index_name> rebuild partition <partition_name> parameters('resume');

begin
ctx_output.end_log;
end;
/

When I ran the alter index for the first partition, I got the error:

ORA-29856: error occurred in the execution of ODCIINDEXDROP routine
ORA-04063: package body "CTXSYS.DRIXMD" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIXMD"

So, I logged into the database as sys and compiled the package:

alter package ctxsys.drixmd compile;

Then I tried the alter index rebuild partition resume again as the index owner.

I was able to complete the alter index for all the partitions. I tried dropping the Oracle text index again, and I was successful.

HTH.