Oracle 11G的DDL_LOCK_TIMEOUT参数

今天kaums给客户做培训11g新特性,发现还真的有不少挺好的新东西,但是以前没有怎么去关注的他们,在后续的几篇中,陆续整理处理.
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_lock_timeout参数

SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter ddl_lock_timeout;
NAME                                 TYPE       VALUE
------------------------------------ ---------- --------------
ddl_lock_timeout                     integer    0

ddl_lock_timeout默认值测试(同以前版本)

--会话1
SQL> set time on
10:27:57 SQL> create table t_xifenfei as
10:28:05   2  select * from dba_users;
Table created.
Elapsed: 00:00:00.08
10:28:17 SQL> delete from t_xifenfei where username='CHF';
1 row deleted.
--会话2
SQL> set timing on
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:00.01

设置ddl_lock_timeout超时测试

--会话1
10:28:17 SQL> delete from t_xifenfei where username='CHF';
1 row deleted.
--会话2
SQL>  ALTER SESSION SET ddl_lock_timeout=10;
Session altered.
Elapsed: 00:00:00.00
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:10.01

设置ddl_lock_timeout未超时测试

--会话2
SQL> ALTER SESSION SET ddl_lock_timeout=30;
Session altered.
Elapsed: 00:00:00.00
--会话1
10:30:00 SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
--会话2
SQL> ALTER TABLE T_XIFENFEI DROP COLUMN AUTHENTICATION_TYPE;
Table altered.
Elapsed: 00:00:07.91

在以前的版本中ddl操作是nowait等待,通过实验可以发现ddl_lock_timeout可以在一定程度上解决因为我们不清楚这个表是否有dml操作而导致ddl操作不能进行的情况,从一定程度上减少了自己去尝试ddl操作,或者查询相关视图然后找出相关会话,然后kill掉对应数据的情况,可以说是在修改表结构的时候一个很不错的新特性.

One thought on “Oracle 11G的DDL_LOCK_TIMEOUT参数”

增加新列测试
在增加列的时候ddl_lock_timeout默认值未报ORA-00054

--session 1
11:32:23 SQL&gt; delete from t_xifenfei where username='CHF';
0 rows deleted.
Elapsed: 00:00:00.00
--session 2
SQL&gt; set time on
11:34:10 SQL&gt; set lines 150
11:34:17 SQL&gt; show parameter ddl_lock_timeout;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- --------
ddl_lock_timeout                     integer                0
11:34:19 SQL&gt; alter table t_xifenfei add (xff varchar2(10));
--hang住
--session 1
11:34:35 SQL&gt; commit;
Commit complete.
Elapsed: 00:00:00.00
--session 2结果
Table altered.
11:37:46 SQL&gt;

设置ddl_lock_timeout未生效

--session 1
11:37:41 SQL&gt; delete from t_xifenfei where username='CHF';
0 rows deleted.
Elapsed: 00:00:00.01
--session 2
11:40:58 SQL&gt; alter session set ddl_lock_timeout=10;
Session altered.
11:41:07 SQL&gt; alter table t_xifenfei add (xff_n  varchar2(10));
--hang住
--session 1
11:41:14 SQL&gt; commit;
Commit complete.
Elapsed: 00:00:00.00
11:42:48 SQL&gt;
--session 2
Table altered.
11:43:37 SQL&gt;

试验证明,在增加新列时,ddl会话不会报ORA-00054错误,同时ddl_lock_timeout参数也未生效

Oracle 11G的DDL_LOCK_TIMEOUT参数

One thought on “Oracle 11G的DDL_LOCK_TIMEOUT参数”

发表评论 取消回复

发表评论取消回复