Adding a Foreign Key Constraint to a Table While DML on another Child Table Can Generate "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired"
(Doc ID 2019693.1)
Last updated on MARCH 03, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Adding a foreign key constraint to a child table can encounter "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" if DML is being performed on another child table
- Creation of a foreign key is prevented on another child table of the same parent table until dml is complete.
- Unless you specify novalidate, in which case the constraint is created and then the session waits on a lock.
For example
Session 1:
Create a parent table with a primary key
Create a child table
Add a foreign key constraint referencing the primary
Insert or a DML(UPDATE/DELETE) on a row into the child and do not commitSession 2:
Create another child table
Add a foreign key constraint referencing the primary
--- this fails with ORA-54
Cause
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
Symptoms |
Cause |
Solution |
References |