Manage Learn to apply best practices and optimize your operations.

How to avoid resource busy error

Occasionally we get the message as 'ORA-00054: resource busy and acquire with NOWAIT specified.' What is the recommended way to get over this issue quickly? Sometimes the issue will go away, but other times we have to do something urgently and it becomes a hindrance.
You get this error message when you are performing some DDL action on an object that is currently locked by another process. Unlike DML actions (INSERT, UPDATE, DELETE), DDL actions will not wait for a lock to be released. Instead, the DDL command aborts telling you that the resource you are trying to reference is locked and the command will not wait for the lock to be released.

Why do DML statements lock out DDL commands? Let's suppose that you have a DML statement like the following:

UPDATE my_table SET column_X = 10;

Before this statement completes, it would be nice to know that someone doesn't drop MY_TABLE or modify COLUMN_X until the UPDATE statement is finished. The UPDATE statement places a lock barring DDL statements from taking place on the object. Once the UPDATE statement is complete, the lock is released.

The reason you are able to retry your operation and have it succeed is that you ran into a time when no DML statements were being issued against the database object you are interested in. Sometimes the wait will be seconds, and other times the wait will be hours. If you cannot wait, then you have to identify which sessions have locked this object, use the V$LOCKED_OBJECT view, and kill those sessions. Keep in mind that killing these sessions may cause some pain to your end users.

Dig Deeper on Oracle database design and architecture