"Alter Table Enable Novalidate Foreign Key Constraint" Gets ORA-00054.
Last updated on DECEMBER 08, 2016
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
***Checked for relevance on 07-Dec-2016***
After migrating from Oracle 10.2 to Oracle 11.2, a problem occurs when attempting to re-enable foreign keys on a table that was loaded.
On a fact table we disable the constraints, then load the table, then enable the constraints with the NOVALIDATE option.
Now it gets an ORA-00054 because "ENABLE NOVALIDATE constraint" needs a lock on the referenced tables (but these are currently locked by other load processes).
After upgrade from 10g to 11g
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