Skip to Main Content
  • Questions
  • Oracle19c expdp fails to export unique indexes that are automatically created via primary key constraint


Question and Answer

Connor McDonald

Thanks for the question, Trent.

Asked: June 03, 2019 - 3:33 pm UTC

Last updated: June 10, 2024 - 6:50 am UTC

Version: 19.3.0

Viewed 10K+ times! This question is

You Asked

I have an issue with the 19c data pump export and I'm wondering if there is a data pump export parameter or some other strategy for resolving it.

When a table's primary key is created as part of the create table statement, a unique index is automatically created with the same name as the primary key.

The 11g data pump exports this unique index separately prior to recreating the constraint.
The 19c data pump does not export the unique index. When the constraint is applied, the index is created automatically.

This creates a problem when an existing (non-unique) index exists that can be used as the index for the primary key constraint. In this case, the constraint is created but it references the incorrect index.

The change manifests itself when the data pump tries to re-import stats into the primary key index. An error is thrown because the primary key index with the expected name (same as the constraint) does not exist.

ORA-20000: Unable to set values for index PK_TABLE_NAME: does not exist or insufficient privileges

Further investigation narrowed down the error to the undocumented flag all_indexes.constraint_index. In 19c, any table that creates the primary key as part of the create table statement has a value of "YES" in this column. This prevents the data pump from creating the index separately.

If the primary key index and constraint are both created separately from the create table statement via the USING clause, all_indexes.constraint_index set to "NO" and the primary key index will be exported by the data pump.

Finally, any table exported by 11g will have the all_indexes.constraint_index set to "NO" regardless of how the primary key index was created. All subsequent exports work as expected. I've confirmed that the all_indexes.constraint_index stays set to "NO".

The attached LiveSQL script recreates the scenario when run on 19c. The schema then needs to be exported / imported to see the issue.

So the problem only happens with tables directly created on 19c AND a pseudo-covering index exists for the primary key constraint. (I say pseudo because the index does not need to be unique and I discovered that the keys can be reversed.) The workaround exists to rewrite the create table statement to not include inline primary key creation but that is impractical for large existing applications. Only rewriting the create table statements for the small number of problematic tables is possible but leaves open the possibility that a future index will cause this scenario to happen.

The ideal solution is a way to either flag the data pump to force the export of all primary key indexes or some way to update all_indexes.constraint_index = "NO". (Effectively two ways of describing the same thing.)

with LiveSQL Test Case:

and Connor said...

Nice investigative work!

I've replicated that on my latest 19c instance.

There are some similar bugs on MOS but none that match exactly so I've logged bug 29892354.


  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Same problem

Curt, June 15, 2020 - 2:20 pm UTC

Hi Connor,
did you get any response on the bug you filed ? , have just created SR 3-23329091521 on the same problem
Connor McDonald
June 16, 2020 - 2:21 am UTC

Yes, patches are being produced for versions and platforms to my knowledge.

For example 23599177 for 19.7 and I think backports are in progress.

Please liaise with Support to get the right patch for your environment/version

Still an issue in 19.14 aparently

Jonas, March 30, 2022 - 4:32 pm UTC

I just ran in to the same problem in 19.14. I can see that the patch exists for 19.13, 19.12, 19.11 etc. Does anyone have any idea if there is a plan to include this fix in a PSU?
Connor McDonald
April 05, 2022 - 4:47 am UTC

I asked internally about this. The reason this has not gone into a formal RU is that RU's are aimed to rolling installable, ie, they can be done on a RAC system in rolling fashion meaning minimal downtime.

The fix for this one is a PLSQL package change, which means if someone was running a DataPump at the time, it could freeze the patch deployment...and thus its been kept separate to reduce risk.

People are on the case and looking at mitigation options.

Any update on 19c getting patched for this?

Jen Cullen, June 05, 2024 - 4:51 pm UTC

Asking for an update. Also curious if there is any work-around other than creating the column and having it's default be a sequence.
Connor McDonald
June 10, 2024 - 6:50 am UTC

Not fixed as of 19.22 :-(

But it is fixed in 21c and above, so I'd get in touch with Support and ask for a backport of the fix.

The base bug is 30978304

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.