DDL LOCK TIMEOUT

ORA-00054

Resource busy is an Oracle error most commonly seen in a 24X7  high traffic database environment. When your tables are being constantly used, you need to wait or find the less busy time to modify the the table structure. What if you don't have time to wait?  You have two options if you can not wait. 

1. Kill the Jobs or Sessions locking your table
2. Set DDL Lock timeout  to 30 Sec. 

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
CREATE UNIQUE INDEX SCOTT.EMPLOYEE_IDX ON SCOTT.EMPLOYEE (EMP_ID)

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

How do you  change the default ddl lock timeout to 30 sec? DDL command requires exclusive lock before it can alter the structure of the database objects. If the object is accessed frequently, you get NOWAIT or ORA-00054 resource busy error message. To get around with this error, you can set ddl_lock_timeout and run the DDL statement.

ALTER SESSION SET ddl_lock_timeout=30; 

CREATE UNIQUE INDEX scott.employee_idx 
  ON scott.employee (emp_id); 

If the 30 sec ddl lock timeout didn't work, I would increase the the time by 10 seconds and re-run the DDL.

What exactly is  DDL_LOCK_TIMEOUT?
According to Oracle Document, DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.If a lock is not acquired before the timeout period expires, then an error is returned.