I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a disabled foreign key from the table I was trying to drop. The referenced table was locked by other sessions, and therefore the DROP TABLE operation failed.
Even if the foreign key constraint is enabled, there is no good reason in my opinion to lock the referenced table; all the more so if it’s disabled.
There is a workaround (which I think proves my last sentence): it’s possible to drop the constraint first, and then to drop the table. Dropping the constraint does not lock the referenced table.
Here is a simple test I executed in 11.2.0.4, 12.1.0.2 and 12.2.0.1:
SQL> create table parents (
2 parent_id number primary key,
3 data varchar2(100)
4 );
Table created.
SQL> create table children (
2 child_id number primary key,
3 parent_id number constraint fk references parents
4 );
Table created.
SQL> insert into parents values (1,null);
1 row created.
SQL> commit;
Commit complete.
In one session we lock the parents table (simply by updating a record):
SQL> -- in session 1 SQL> update parents set data=null; 1 row updated.
And now in another session we try to drop the children table:
SQL> -- in session 2
SQL> drop table children purge;
drop table children purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
It failed (because it tried to lock the parents table in “share” mode).
Let’s disable the foreign key constraint (note that this operation does not lock the parents table), and retry dropping the table:
SQL> alter table children disable constraint fk;
Table altered.
SQL> drop table children purge;
drop table children purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Still no success.
Let’s drop the constraint (again, this operation does not lock the referenced table), and retry dropping the table:
SQL> alter table children drop constraint fk;
Table altered.
SQL> drop table children purge;
Table dropped.
And now we succeeded (well, now it really had no excuses to fail).
Update
When I initially wrote this post, I thought that this behavior had been fixed in 12c. The reason is that I was careless and ignored one crucial difference between my 11g and 12c environments – in my 11g database the recyclebin was off, while in my 12c database it was on. And I used the DROP TABLE statement without the PURGE option.
Thanks to Jonathan Lewis, Liron Amitzi, Friedhold Matz and Franck Pachot, who repeated my tests in their environments, I found the flaw in my original post and fixed it.
This actually led to another workaround – if your recyclebin is on, then you can first drop the table without specifying the purge keyword (so the table is just “moved” to the recyclebin) and then purge the table.