My Oracle Support Banner

Why Does The Primary Key Constraint Point To A Different Index After Import? (Doc ID 1455492.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 19-Nov-2015***

Goal

You started DataPump export/import to transfer a table with indexes and constraints to another database. After import, you observed that the primary key constraint is using a different index than the originally defined one.

The next test demonstrates this:

connect / as sysdba

create or replace directory tmp as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

connect test/test

drop table a_tab purge;

create table a_tab
(
   id number,
   text varchar2(10)
);
insert into a_tab values (1, 'Text 1');
commit;

-- add a PK constraint
alter table a_tab add primary key (id);

-- create an index
create index a_ind on a_tab (id, text);

-- identify what index does the PK use
col tab format a20
col constraint_name format a20
col ind format a20
select owner||'.'||table_name tab,
       constraint_name, constraint_type,
       index_owner||'.'||index_name ind
from   user_constraints
where  table_name = 'A_TAB';

TAB                  CONSTRAINT_NAME      C IND
-------------------- -------------------- - --------------------
TEST.A_TAB           SYS_C006195          P TEST.SYS_C006195


Now export the database/schema/table:

#> expdp system/password directory=tmp dumpfile=a_tab.dmp tables=test.a_tab exclude=statistics

 

Export: Release 11.2.0.3.0 - Production on Fri May 4 11:16:26 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=tmp dumpfile=a_tab.dmp tables=test.a_tab exclude=statistics reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."A_TAB"                              5.414 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tmp/a_tab.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 11:16:35


Import into target database:

#>  impdp system/password directory=tmp dumpfile=a_tab.dmp tables=test.a_tab

 

Import: Release 11.2.0.3.0 - Production on Fri May 4 11:17:04 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=tmp dumpfile=a_tab.dmp tables=test.a_tab
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."A_TAB"                              5.414 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 11:17:08


Identify what index does the PK use after import:

TAB                  CONSTRAINT_NAME      C IND
-------------------- -------------------- - --------------------
TEST.A_TAB           SYS_C006210          P TEST.A_IND


=> Now the PK constraint with system generated name SYS_C006210 uses the non unique index A_IND.

Explanation

Export puts the DDLs into the dump in the following order:

1. CREATE TABLE statement
2. CREATE INDEX statements (see <bug 6156708>, this must be fixed in source database before running the export)
3. ALTER TABLE ADD CONSTRAINT statements

During import, this sequence order is kept. When adding a primary/unique key constraint (step 3), Oracle checks if any existing index can be utilized to enforce the constraint, and in that case, uses it instead of creating a unique index (which was done before in 7.x releases unconditionally). This was done to minimize disk utilization and time needed for the constraint to be enabled.

In the example above, the primary key constraint has a system generated name. So the adjacent CREATE UNIQUE INDEX statement (with same system generated name) will be missing from dump file (step 2).

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.