How to set a timeout for DDL operations in oracle11g - to avoid "resource busy" error

ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;

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 is successful or the time expires, whichever comes first.

For exammple, if the following query is executed:


SQL> alter table sales add (tax_code varchar2(10));

the statement hangs and does not error out.

Arup Nanda puts it as

somewhat like a telephone programmed to re-try a busy number

To set it database-wide:

ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10;