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

Last updated on FEBRUARY 22, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

1 CREATE INDEX "SCOTT"."INDEX1" ON  MYTABLE (OPPRETTETDATO ASC, KORTNR ASC, STAN ASC, U##AID,VERSJONSNR )
  2 GLOBAL PARTITION BY RANGE (OPPRETTETDATO)
  3 (
  4 PARTITION P_2016_01 VALUES LESS THAN (TO_DATE('01-02-2016','DD-MM-YYYY')) COMPRESS
  5 , PARTITION P_2016_02 VALUES LESS THAN (TO_DATE('01-03-2016','DD-MM-YYYY')) COMPRESS
  6 , PARTITION P_2016_03 VALUES LESS THAN (TO_DATE('01-04-2016','DD-MM-YYYY')) COMPRESS
  7 , PARTITION P_2016_04 VALUES LESS THAN (TO_DATE('01-05-2016','DD-MM-YYYY')) COMPRESS
  8 , PARTITION P_2016_05 VALUES LESS THAN (TO_DATE('01-06-2016','DD-MM-YYYY')) COMPRESS
  9 , PARTITION P_2016_06 VALUES LESS THAN (TO_DATE('01-07-2016','DD-MM-YYYY')) COMPRESS
 10 , PARTITION P_2016_07 VALUES LESS THAN (TO_DATE('01-08-2016','DD-MM-YYYY')) COMPRESS
 11 , PARTITION P_2016_08 VALUES LESS THAN (TO_DATE('01-09-2016','DD-MM-YYYY')) COMPRESS
 12 , PARTITION P_2016_09 VALUES LESS THAN (TO_DATE('01-10-2016','DD-MM-YYYY')) COMPRESS
 13 , PARTITION P_2016_10 VALUES LESS THAN (TO_DATE('01-11-2016','DD-MM-YYYY')) COMPRESS
 14 , PARTITION P_2016_11 VALUES LESS THAN (TO_DATE('01-12-2016','DD-MM-YYYY')) COMPRESS
 15 , PARTITION P_2016_12 VALUES LESS THAN (TO_DATE('01-01-2017','DD-MM-YYYY')) COMPRESS
 16 , PARTITION P_2017_01 VALUES LESS THAN (TO_DATE('01-02-2017','DD-MM-YYYY')) COMPRESS
 17 , PARTITION P_2017_02 VALUES LESS THAN (TO_DATE('01-03-2017','DD-MM-YYYY')) COMPRESS
 18 , PARTITION P_MAX VALUES LESS THAN (MAXVALUE) COMPRESS
 19 )
 20 LOGGING
 21 TABLESPACE TSIDX7
 22 STORAGE
 23 (
 24 INITIAL 67108864
 25 NEXT 67108864
 26 MAXEXTENTS UNLIMITED
 27 BUFFER_POOL DEFAULT
 28* ) PARALLEL 4 COMPRESS 5 ONLINE
SYSTEM@p1011cas SQL>/
  
Index created.

SYSTEM@p1011cas SQL> alter index "INDEX1" noparallel;
  alter index "INDEX1" noparallel
*
ERROR at line 1:
ORA-14415: index in partially dropped state, submit DROP INDEX



*** 2017-01-23 15:51:26.267
*** SESSION ID:(1164.15408) 2017-01-23 15:51:26.267
*** CLIENT ID:() 2017-01-23 15:51:26.267
*** SERVICE NAME:(SYS$USERS) 2017-01-23 15:51:26.267
*** MODULE NAME:(DBMS_SCHEDULER) 2017-01-23 15:51:26.267
*** CLIENT DRIVER:() 2017-01-23 15:51:26.267
*** ACTION NAME:(CLEANUP_ONLINE_IND_BUILD) 2017-01-23 15:51:26.267
*** CONTAINER ID:(3) 2017-01-23 15:51:26.267
 
online index (re)build cleanup: objn=99036 maxretry=2000 forever=0

*** 2017-01-23 15:57:06.512
ORA-12012: error on auto execute of job "SYS"."CLEANUP_ONLINE_IND_BUILD"
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_PDB", line 76
ORA-06512: at line 4

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms