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

The error is raised when the following statement is executed against the Oracle Database.

SQL> truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Cause: 

The statement requires an exclusive lock on the table.If the table is active and has DML or DDL transactions against it the transaction will not be able to acquire the lock quickly. This will cause the transaction to fail and return the resource busy error to the session.

Solution:

Using DDL_LOCK_TIMEOUT

You can increased the timeout by modifying the DDL_LOCK_TIMEOUT parameter in your session. Once this is set Oracle will wait for the new TIMEOUT before returning the “ORA-00054: resource busy and acquire with NOWAIT specified” error.

[ora@sonu ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET ddl_lock_timeout=900;
Session altered.