Re: ORA-00054: resource busy... - what on Earth?

> DROP TABLE project CASCADE CONSTRAINTS
> *
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT specified

  If you issue an update statement against a table then those rows affected will get locked (until the lock is released by COMMIT or ROLLBACK). When you try to drop the affected table while the lock is still there Oracle raises this error because both interfere with each other. The block(s) locked by the update would get altered (namely: deleted) by the DROP TABLE statement. The DROP TABLE statement hence tries to acquire a lock on the same block(s) which yields you ORA-00054. It's perfectly natural.

  To resolve this, COMMIT or ROLLBACK the transaction that does the UPDATEs. after that, you should be a ble to drop... unless there is yet another lock on the table.

  Use SYS.V_$LOCKED_OBJECT or similar to find out more about recent locks in your instance.

  It is not necessary to restart your instance when you run into a (dead)lock. Just killing the connection(s) in question should do.

AH
:) Received on Thu Aug 05 2004 - 07:19:21 CDT