联系:手机/微信(+86 17813235971) QQ(107644445)
标题: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掉对应数据的情况,可以说是在修改表结构的时候一个很不错的新特性.
增加新列测试
在增加列的时候ddl_lock_timeout默认值未报ORA-00054
设置ddl_lock_timeout未生效
试验证明,在增加新列时,ddl会话不会报ORA-00054错误,同时ddl_lock_timeout参数也未生效