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

From: Neil Chandler <>
Date: Tue, 1 May 2018 07:56:03 +0000
Message-ID: <HE1PR1001MB1243750D60E33224CD4B3A1885810_at_HE1PR1001MB1243.EURPRD10.PROD.OUTLOOK.COM>

I wrote about this a year ago. Not clever, Oracle!

sent from my phone

On 1 May 2018, at 01:25, Chris Taylor <<>> wrote:

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


Received on Tue May 01 2018 - 09:56:03 CEST

Original text of this message