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

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

https://chandlerdba.com/2017/05/12/online-index-rebuild-problem-in-12c/

Neil.
sent from my phone

On 1 May 2018, at 01:25, Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>> 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 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