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.
该参数是oralce 11g中才有的;
实验:
\--session 1
SQL> select \* from v$version;
BANNER
\--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter ddl
NAME TYPE VALUE
\------------------------------------ ----------- ------------------------------
ddl\_lock\_timeout integer 0
enable\_ddl\_logging boolean FALSE
\--session 2
SQL> insert into t\_1 values('b');
1 row created.
\--session 3
SQL> alter table t\_1 modify(a varchar2(50));
alter table t\_1 modify(a varchar2(50))
\*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
该错误是执行之后没有等待直接出现;
\--session 1
SQL> alter system set ddl\_lock\_timeout=60;
System altered.
SQL> show parameter ddl
NAME TYPE VALUE
\------------------------------------ ----------- ------------------------------
ddl\_lock\_timeout integer 60
enable\_ddl\_logging boolean FALSE
\--session 2
SQL> insert into t\_1 values('a');
1 row created.
\--session 3
SQL> alter table t\_1 modify (a varchar2(30));
alter table t\_1 modify (a varchar2(30))
\*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
该错误是在等待了60秒之后才出现;
\--session 3
SQL> alter table t\_1 add c number;
则会一直等待下去,直到请求的锁资源被释放(该变量不起作用);
在网上有些文章说,如果是添加列回立即提交,不敢苟同;