Oracle 11G的ddl_lock_timeout参数

Oracle 11G里多出了ddl_lock_timeout,在你执行DDL操作,如果没法获得锁的话,指定等待几秒钟,缺省值是0秒,即不等待.还是举个例子:

session a:

SQL> create table t as select * from dba_users;

Table created.

SQL> insert into t select * from t;

31 rows created.

SQL>

 

session b:

SQL> show parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL>
SQL> alter table t move;
alter table t move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:00.00  --因没法获得独占锁,立即返回失败
SQL>
SQL> alter system set ddl_lock_timeout=10 --指定等待10秒钟
  2  ;

System altered.

Elapsed: 00:00:00.00
SQL> alter table t move;
alter table t move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:10.01  --因没法获得独占锁,但等待10秒后才返回失败

 

这个参数也可以session级别指定:

SQL> alter session set ddl_lock_timeout=0;

 

作者:George.ma  Blog:http://blog.chinaunix.net/u/12521/