Home -> Mailing Lists -> Oracle-L -> Be aware: ORA-14415: Index In Partially Dropped State, Submit DROP INDEX On Newly Created Index (Doc ID 2226253.1)

Be aware: ORA-14415: Index In Partially Dropped State, Submit DROP INDEX On Newly Created Index (Doc ID 2226253.1)

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Mon, 30 Apr 2018 18:35:10 -0500
Message-ID: <CAP79kiT-VeDwTus0DgpRAutb4zkpX6YV8rpxKs3qVM=0pdOM1g_at_mail.gmail.com> 





(Alternative working title: How your expensive, top of the line Enterprise


Database can drive you bonkers)



I ran into this over the weekend during non-business hours - on a 42GB
table with 7,744 partitions.



Oracle delivers an autotask job in 12.1.0.2(+) called:
SYS.CLEANUP_ONLINE_IND_BUILD.


Guess what happens if you create an index online while that job is
running?  Your index gets borked.



Unless there was something wrong with my index build, which I don't think
there is:



create index redacted.claim_elig_evt_q_idx01 on redacted.claim_elig_evt_q


(contract_id) invisible online parallel 16 local


/



Index created.



Elapsed: 00:43:04.52



alter index redacted.claim_elig_evt_q_idx01   noparallel
/


alter index redacted.claim_elig_evt_q_idx01   noparallel
*


ERROR at line 1:


ORA-14415: index in partially dropped state, submit DROP INDEX



Why Oracle would design an AUTOTASK job that can break online index builds
is beyond comprehension.



Chris


--
http://www.freelists.org/webpage/oracle-l


Received on Tue May 01 2018 - 01:35:10 CEST












Original text of this message