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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 1 May 2018 13:43:49 -0400
Message-ID: <040f01d3e173$f7103700$e530a500$_at_rsiz.com>



Efforts toward acceptance of full operational automation are aided by driving highly competent human competition of achieving optimal behavior closer to the edge of sanity.  

Would “automation” have even detected a gain to be had from this rebuild? No? Then no problem for the machine, just for humans.  

“bonkers”  

Well played, Larry.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Neil Chandler Sent: Tuesday, May 01, 2018 3:56 AM
To: christopherdtaylor1994_at_gmail.com
Cc: ORACLE-L
Subject: 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> 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
Received on Tue May 01 2018 - 19:43:49 CEST

Original text of this message