DataPump – niche fix – Learning is not a spectator sport

DataPump – niche fix

January 15, 2025

4–6 minutes

On a Office Hours session a while back, someone brought up this niche issue when use DataPump and you have “overlapping” indexes.

What do I mean by “overlapping indexes”? Consider the following example. Notice that I have two indexes (one implicitly create as part of the constraint definition, and one explicitly created with CREATE INDEX) both of which could satisfy as an underlying index to support the function of the constraint.


SQL> create user demo identified by demo quota 100m on users;

User created.

SQL>
SQL> grant resource, connect to demo;

Grant succeeded.

SQL>
SQL> grant read,write on directory ctmp to demo;

Grant succeeded.

SQL>
SQL> conn demo/demo
Connected.
SQL> create table cons_in_place
  2      (
  3        subject_id number not null enable,
  4        id         number not null enable,
  5        manager_id number,
  6        constraint pk_cons_in_place primary key (subject_id, id)
  7      );

Table created.

SQL>
SQL> create index idx_wrong_index
  2  on cons_in_place (subject_id, id, manager_id) ;

Index created.

SQL>
SQL> select table_name, index_name, uniqueness, constraint_index FROM user_indexes;

TABLE_NAME                     INDEX_NAME                     UNIQUENES CON
------------------------------ ------------------------------ --------- ---
CONS_IN_PLACE                  PK_CONS_IN_PLACE               UNIQUE    YES
CONS_IN_PLACE                  IDX_WRONG_INDEX                NONUNIQUE NO

Because I created the constraint first, and the standalone index second, both objects now exist. Now I export the table, drop the table and re-import


SQL> host expdp demo/demo directory=ctmp dumpfile=demo.dmp

Export: Release 19.0.0.0.0 - Production on Tue Jan 14 15:22:11 2025
Version 19.22.0.0.0

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

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/******** directory=ctmp dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEMO"."CONS_IN_PLACE"                          0 KB       0 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  /tmp/demo.dmp
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 14 15:26:04 2025 elapsed 0 00:03:03


SQL> drop table cons_in_place cascade constraints purge;

Table dropped.

SQL> host impdp demo/demo directory=ctmp dumpfile=demo.dmp

Import: Release 19.0.0.0.0 - Production on Tue Jan 14 15:31:34 2025
Version 19.22.0.0.0

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

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/******** directory=ctmp dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."CONS_IN_PLACE"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Tue Jan 14 15:32:00 2025 elapsed 0 00:00:23

You would expect absolutely no change to the schema with a simple export/import, but look at the number of indexes in my schema after the import.


SQL> select table_name, index_name, uniqueness, constraint_index FROM user_indexes;

TABLE_NAME                     INDEX_NAME                     UNIQUENES CON
------------------------------ ------------------------------ --------- ---
CONS_IN_PLACE                  IDX_WRONG_INDEX                NONUNIQUE NO

SQL>

In earlier versions of DataPump, because indexes were created first and then constraints added after the fact, we have reversed the order of the creation you saw above in the initial schema creation. As a result, the database decided the existing index was fine to satisfy the constraint.

While I’m willing to bet the chances of you being impacted by this bug were probably near zero, you’ll please to know this has been fixed in recent versions of DataPump,

Here the same output from a more recent RU


SQL> create user demo identified by demo quota 100m on users;

User created.

SQL>
SQL> grant resource, connect to demo;

Grant succeeded.

SQL>
SQL> grant read,write on directory ctmp to demo;

Grant succeeded.

SQL>
SQL> conn demo/demo@pdb21a
Connected.
SQL> create table cons_in_place
  2      (
  3        subject_id number not null enable,
  4        id number not null enable,
  5        manager_id number,
  6          constraint pk_cons_in_place primary key (subject_id, id)
  7      );

Table created.

SQL>
SQL> create index idx_wrong_index
  2  on cons_in_place (subject_id, id, manager_id) ;

Index created.

SQL>
SQL> select table_name, index_name, uniqueness, constraint_index FROM user_indexes;

TABLE_NAME                     INDEX_NAME                     UNIQUENES CON
------------------------------ ------------------------------ --------- ---
CONS_IN_PLACE                  PK_CONS_IN_PLACE               UNIQUE    YES
CONS_IN_PLACE                  IDX_WRONG_INDEX                NONUNIQUE NO

2 rows selected.

SQL> host expdp demo/demo@pdb21a directory=ctmp dumpfile=demo.dmp

Export: Release 23.0.0.0.0 - Production on Tue Jan 14 15:13:38 2025
Version 23.4.0.24.05

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "c:\tmp\demo.dmp"
ORA-27038: created file already exists
OSD-04010:  option specified, file already exists



SQL> host del c:\tmp\demo.dmp

SQL> host expdp demo/demo@pdb21a directory=ctmp dumpfile=demo.dmp

Export: Release 23.0.0.0.0 - Production on Tue Jan 14 15:13:48 2025
Version 23.4.0.24.05

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/********@pdb21a directory=ctmp dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEMO"."CONS_IN_PLACE"                          0 KB       0 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  C:\TMP\DEMO.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 14 15:14:14 2025 elapsed 0 00:00:25


SQL> drop table cons_in_place cascade constraints purge;

Table dropped.

SQL>
SQL> host impdp demo/demo@pdb21a directory=temp dumpfile=demo.dmp

Import: Release 23.0.0.0.0 - Production on Tue Jan 14 15:14:16 2025
Version 23.4.0.24.05

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/********@pdb21a directory=temp dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."CONS_IN_PLACE"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Tue Jan 14 15:14:26 2025 elapsed 0 00:00:10


SQL>
SQL> select table_name, index_name, uniqueness, constraint_index FROM user_indexes;

TABLE_NAME                     INDEX_NAME                     UNIQUENES CON
------------------------------ ------------------------------ --------- ---
CONS_IN_PLACE                  IDX_WRONG_INDEX                NONUNIQUE NO
CONS_IN_PLACE                  PK_CONS_IN_PLACE               UNIQUE    YES

2 rows selected.

SQL>
SQL>

Surprise, surprise the moral of the story is … stay up to date on patches!

2 responses to “DataPump – niche fix”

iudithd5bf8e4d8d

January 15, 2025 at 7:14 pm
Hello Connor,

As by the log of the import, the patched version still shows that the indexes are created before the constraints.
I guess that what the fix did in fact was to build first the indexes that “will support” constraints, then the other indexes.

This is a “catch” by itself, because, if you do the following:
1. create the table WITHOUT the pk
2. create the two indexes
3. alter table ADD primary key ( without a USING INDEX clause )
then the “ADD primary key” will always pick up the index that was created FIRST among the two indexes that could both support the constraint,
even if that is a non-unique index.

So, for a “complete” fix of the issue, the export should “remember” which of the two indexes is supporting the constraint.

The USER_INDEXES.CONSTRAINT_INDEX is not relevant in this case, as it shows “NO” for both indexes.

So, it should “go forward” to USER_CONSTRAINTS. INDEX_NAME to see what is the index that should be built first.

It’s interesting how the import is now doing this, if it (still) processes constraints AFTER the indexes.

Cheers & Best Regards,
Iudith
Reply
Евгений Пузиков

January 16, 2025 at 4:37 pm

I have faced the same on 19.20 on migration using datapump.
I added the index manually after migration.

Reply

Learning is not a spectator sport