This discussion is archived
1 Reply Latest reply: Feb 13, 2012 11:19 AM by mannamal RSS

Resolve without using "drop force" - domain Index marked as loading

104998 Newbie
Currently Being Moderated
Hi,

db - 10g R2 (10.2.0.4.0) on RHEL Linux.

We have a LOCAL PARTITIONED domain index on a table with 400 million rows (500 GB table size). Range partitioned by date (1 partition per day).

The command to build one partition of the index failed and left the index in "INPROGRS" state. We can't afford to "drop force" the index and rebuild from scratch as it takes 10 to 12 hours to do so making the system "uselss/unavailable" (performance wise).

When I try to rerun the command again after fixing the error in the command we get following error -

alter index VM_IDX REBUILD PARTITION VM_IDX_20120212 PARAMETERS('TABLESPACE=VM_IDX_20120212 WORK_TABLESPACE=TS')


ORA-29868: cannot issue DDL on a domain index marked as LOADING


Is there a way to get out of this situation without dropping the index with force option and rebuilding from scratch?

SQL> SELECT index_name, status, domidx_status, domidx_opstatus FROM user_indexes WHERE index_name = 'VM_IDX';

INDEX_NAME STATUS DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
VM_IDX INPROGRS VALID VALID

Thanks in advance,
Paresh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points