Why Does The Primary Key Constraint Point To A Different Index After Import?
(Doc ID 1455492.1)
Last updated on MARCH 04, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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:
=> Now the PK constraint with system generated name SYS_C006210 uses the non unique index A_IND.
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).
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