'I Cant delete constraints of table so i can drop it. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I want to delete foreign key constraints in sqldeveloper so i can drop all tables, but it shows me the error in title when i try to do it. someone help?

sqloracle-sqldeveloper


Solution 1:[1]

The table is currently being used; someone (maybe even you, in another session) performed DML on it and didn't commit (nor rollback) so the table is "busy". Once that transaction is done (by either committing or rolling back), you'll be able to drop that foreign key constraint.

Here's an example that shows two sessions that run in parallel; follow the numbers:

enter image description here

  1. user A created the table that has a foreign key constraint to the DEPT table
  2. user B - in another session, obviously - inserted a row into that table
  3. user A tried to drop the constraint but was unable to because the table is still "busy"
  4. user B commits
  5. user A is now able to drop the constraint

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot

Related Questions