ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Reason:

The error message says it all: the table is being used right now, probably by a transaction, so getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table are not locked forever. When sessions perform commit the locks on those rows are released, but before that unlock period gets very far, other sessions may update some other rows of the table—and thus the slice of time to get the exclusive lock on the table vanishes. In a typical business environment, the window for locking the table exclusively does open periodically, but the DBA may not be able to perform the alter command exactly at that time.

Solution

In Oracle Database 11g, there is a better option: the DDL Wait option.

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation
until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE SAID.STFACMAS
ADD (OVRDUE_APP VARCHAR2 (1 BYTE) DEFAULT 'N');