Re: ORA-00054: resource busy and acquire with NOWAIT specified

Comments embedded

On 6 Oct 2005 09:22:08 -0700, "cumin" <jkilbourne_at_gmail.com> wrote:

>A table was updating, taking far longer than I expected. In sql*plus, I
>hit Ctl-C to abort the update,

so you forced Oracle to rollback your transaction

but was exited out of sql*plus as well.
>I then tried to modify the table (to remove a column), and received
>
>ORA-00054: resource busy and acquire with NOWAIT specified
>

Your table is still in use. DDL statements require a lock on the corresponding records in the datadicionary.

>I had never specified NOWAIT (I don't even know what NOWAIT does).

You can look that up in the documentation.  I
>executed "commit" thinking that would take care of it, and Oracle would
>give me back control of the table, but that didn't work.

Obviously, as commit affects only the current transaction, and the transaction you aborted is a *different* transaction.

 I moved the
>data to another table and tried to drop the original table, but again
>received the error. I'm not even sure if exiting Oracle in the middle
>of the update related to this error.

Sure it is. Oracle was still rolling back your aborted transaction.

>
>How can I drop this table?

You need to make sure it is no longer accessed by any process. So you need to wait until Oracle has performed a full rollback on the transaction you aborted.

--
Sybrand Bakker, Senior Oracle DBA