I'm on 12.1.0.1 Enterprise Edition.
I've got a very large table of sdo_geometry objects which I've split up into many partitions. I build a spatial index on this table as UNUSABLE and then rebuild the partitions across a few separate sessions.
In total the index build takes a few days. Last week during the index build, the database crashed completely. That left me with most of the partitions with a USER_IND_PARTITIONS.STATUS of USABLE, some with UNUSABLE and a couple with INPROGRS.
I can rebuild the UNUSABLE partitions as normal, but when I try to build an INPROGRS partition I get:
ERROR at line 1:
ORA-29952: cannot issue DDL on a domain index partition marked as LOADING
The error is understandable given that the index partition was INPROGRS when the database crashed. The question is how to recover from it. The doco says:
Wait till the index partition operation completes OR issue a DROP INDEX FORCE to drop the index OR issue a ALTER TABLE DROP PARTITION to drop the partition.
Neither of these options are good for me. I don't want to drop the entire index as most of it is built. There doesn't seem to be any option to recover from an index partition build that is stuck at INPROGRS.
Support Doc ID 557600.1 deals with this exact issue in the context of Oracle Text indexes. Its says:
Occasionally, if CREATE INDEX or ALTER INDEX fails, an index may be left with the status LOADING or INPROGRS and leaves the context index in an unusable state.
Any attempt to recover using RESUME INDEX is blocked. The only recourse is to drop and recreate the index.
For this situation, use CTX_ADM.MARK_FAILED to force the index status from LOADING to FAILED so that you can recover the index with RESUME INDEX.
So the suggestion is to use CTX_ADM.MARK_FAILED ( http://docs.oracle.com/database/121/CCREF/cadmpkg.htm#CCREF0500 ).
Is there any similar option for spatial indexes?
Thanks,
John