sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

学习笔记:Oracle 11G新功能DDL_LOCK_TIMEOUT参数案例详解

时间:2016-06-15 11:25   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 通过案例,详见记录关于Oracle 11G新功能DDL_LOCK_TIMEOUT参数详解

1.数据库ddl_lock_timeout参数

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.

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

2.ddl_lock_timeout默认值案例测试(同以前版本)

--会话1
SQL> set time on
10:27:57 SQL> create table t_oracleplus as
10:28:05   2  select * from dba_users;

Table created.

Elapsed: 00:00:00.08
10:28:17 SQL> delete from t_oracleplus where username='CHF';

1 row deleted.

--会话2
SQL> set timing on
SQL> ALTER TABLE T_oracleplus DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_oracleplus 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

3.设置ddl_lock_timeout超时案例测试

--会话1
10:28:17 SQL> delete from t_oracleplus 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_oracleplus DROP COLUMN AUTHENTICATION_TYPE;
ALTER TABLE T_oracleplus 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

4.设置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_oracleplus DROP COLUMN AUTHENTICATION_TYPE;

Table altered.

Elapsed: 00:00:07.91

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


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 11G新功能DDL_LOCK_TIMEOUT参数案例详解

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/384.html

Oracle研究中心

关键词:

Oracle 11G新功能DDL_LOCK_TIMEOUT

参数DDL_LOCK_TIMEOUT案例详解