11g的DDL_LOCK_TIMEOUT参数的作用,从字面上看,就是DDL获取锁的超时时间。
先看下官方文档的介绍:
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.
从解释看,很好理解,该参数表示了执行DDL语句时需要等待DML锁的超时时间,默认0表示当无法立即获得DML锁时,会立即抛出NOWAIT,并且支持session级修改。
实验:
SQL> show parameter ddl_lock_timeout NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 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