DataPump Import (IMPDP) Returns Error ORA-31684 Object Type INDEX Already Exists For Primary Key Constraints (Doc ID 1433123.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 12-Aug-2013***

Symptoms

You started a DataPump import (IMPDP) and received errors like:

...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."A_TAB":"P001" 0 KB 0 rows
. . imported "TEST"."A_TAB":"P002" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"TEST"."A_TAB_PK" already exists
...


You checked the DDL statements in SQLFILE extracted from dump file and observed, the primary key constraint is already present inline in column list (what implicitly creates the unique index with same name). Additionally a unique index is created:

CREATE TABLE "TEST"."A_TAB"
( "ID" NUMBER NOT NULL ENABLE,
"TEXT" VARCHAR2(10 BYTE),
CONSTRAINT "A_TAB_CHK" CHECK (text is not null) ENABLE,
CONSTRAINT "A_TAB_PK" PRIMARY KEY ("ID")       ==> inline PK constraint
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
....

==> Additional CREATE UNIQUE INDEX statement
CREATE UNIQUE INDEX "TEST"."A_TAB_PK" ON "TEST"."A_TAB" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;


During the execution (import), the additional CREATE UNIQUE INDEX statement generates the error ORA-31684.

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