Identify and Eliminate Referential Constraints to Prevent ORA-02049 Errors (Doc ID 1493327.1)

Last updated on DECEMBER 07, 2022

Applies to:

Symptoms

 An error similar to this:

 These can be caused by insufficient indices against all tables.

You need to discover all tables that have a referential constraint against them (ie. they have a foreign reference) and verify that they have sufficient indexing. Databases work by creating internal share locks when working with referential constraints when there is no index on the foreign key that references another tables primary key. If there is no such index on these tables, then operations such as insert, delete or update on the parent's primary key will cause frequent waits on the systems internally acquired share locks on those tables. Although the specific index may not be required, it is good practice to ensure all these indices are created.

Changes

Cause

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