DDL_LOCK_TIMEOUT, ddl_lock_timeout

DDL_LOCK_TIMEOUT, ddl_lock_timeout

The function of the 11g DDL_LOCK_TIMEOUT parameter is literally the timeout time for DDL to obtain the lock.

Let's take a look at the introduction of the official documentation:

DDL_LOCK_TIMEOUT

Property	Description
Parameter type	Integer
Default value	0
Modifiable	ALTER SESSION
Range of values	0 to 1,000,000 (in seconds)
Basic	No

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.

From the explanation, it is easy to understand. This parameter indicates the timeout time to wait for the DML lock when executing the DDL statement. The default value 0 indicates that when the DML lock cannot be obtained immediately, NOWAIT will be thrown immediately, it also supports session-level modification.

Lab:

SQL> show parameter ddl_lock_timeout

NAME TYPE VALUE
-----------------------------------------------------------------------------
Ddl_lock_timeout integer0

SQL> desc t;

Name Null? Type
-----------------------------------------------------------------------------
Tid not null number (38)
CLS_NAME not null VARCHAR2 (30)

Session1:

SQL> update t set cls_name = 'A' where tid = 1;

0 rows updated.

Session2:

SQL> drop table t;

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

Elapsed: 00:00:00. 00

Session2:

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

SQL> drop table t;
Drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:10. 01