Wait option for Oracle 11g DDL (DDL_LOCK_TIMEOUT)

The DDL command requires the internal structure of an exclusive lock. If these locks are not available, a "ORA-00054: resource busy" is returned, which can be particularly frustrating when trying to modify frequently accessed objects. To solve this problem, Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, using the alter system and alter session commands at the instance or session level, respectively.

DDL_LOCK_TIMEOUT indicates the number of seconds a DDL command waits for a lock before throwing a "resource busy" error. The default value is 0 (for NOWAIT).

SQL > create table ddl_lock_test (

Id number

);

SQL > insert into ddl_lock_test values (1)

-create a test table and insert a piece of data, but not commit

-create a new session, set DDL_LOCK_TIMEOUT to a non-zero value at the session level and try to add a column to the table

SQL > alter session set ddl_lock_timeout = 20

SQL > alter table ddl_lock_test add (

Name varchar2 (20)

);

-this session will wait 20 seconds before failing.

Alter table ddl_lock_test add (name varchar2 (20))

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

-if we repeat the alter table command and commit the insert of the first session within 20 seconds, alter table will return an execution success message.

SQL > alter table ddl_lock_test add (

Name varchar2 (20)

);

Table altered.

SQL >

For more information, please refer to the official document: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm