ORACLE 11g新特性-允许DDL锁等待DML锁

admin

之前也写了一些关于ORACLE11g新特性的文章，现在ORACLE 11g已成为主流的ORACLE数据库版本，了解和学习ORACLE 11g的新特性至关重要，本人也是ORACLE 11g新特性的初学者，在此分享下我的学习过程和心得。
本文主要记录的是ORACLE 11g的一个新特性，允许DDL锁等待DML锁，这也是在6月30日，张乐奕（kamus）老师在ACOUG活动中分享的一个主题。
在11g之前的版本，默认情况下，DDL锁都不等待DML锁，在一个存在DML锁的表上执行DDL操作，会立即返回失败（同一SESSION除外）,下面简单做下试验，在SESSION1向表STREAM中插入数据，不要提交，此时表STREAM会存在DML锁，在SESSION2执行TRUNCATE表STREAM操作，就会立即返回失败：
SESSION1 >insert into stream select * from dbdream; 10 rows created. SESSION2 >truncate table stream; truncate table stream * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 在11g版本，ORACLE推出了DDL_LOCK_TIMEOUT参数，允许DDL锁等待DML锁，该参数控制DDL锁等待DML锁的时间，单位为秒，默认值为0，即DDL锁不等待DML锁，最大值是100万，也就是11.5天，该参数可以全局设置，也可以在SESSION级设置。
SESSION2 >show parameter ddl_lock_timeout NAME TYPE VALUE ----------------------- ----------- ------ ddl_lock_timeout integer 0 SESSION2 >alter session set ddl_lock_timeout=2000000; ERROR: ORA-00068: invalid value 2000000 for parameter ddl_lock_timeout, must be between 0 and 1000000 下面演示下DDL_LOCK_TIMEOUT参数的效果，还拿以上STREAM表为例，目前STREAM表仍然存在DML锁，在SESSION2将DDL_LOCK_TIMEOUT设置为60秒，然后再执行TRUNCATE操作，此时不会马上返回失败，而会等待60秒的时间，如果60秒内STREAM表的DL锁没有释放，才会返回失败。
SESSION2 >alter session set ddl_lock_timeout=60; Session altered. SESSION2 >set timing on SESSION2 >truncate table stream; --此时，SESSION2挂起，在60秒的时间内等待SESSION1释放DML锁，60秒后返回失败 truncate table stream * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:01:00.01 DDL_LOCK_TIMEOUT参数的出现，极大简化了DBA的操作，比如我们要在一张表上增加个字段，可是这张表有大量的DML操作，并且我们不能使用ALTER SYSTEM KILL SESSION的方法结束这些操作，那么我们想要在这张表上增加字段就需要无数次的执行增加字段的操作，或者写脚本去干这个活，现在有了DDL_LOCK_TIMEOUT参数，我们只需要将这个参数的值设置的稍大一点，执行一下SQL就不用管啦，下面演示下这个过程。
SESSION2增加字段：
SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/'; 此时挂起，等待STREAM表的DML锁释放，SESSION1提交释放DML锁后，SESSION2操作便成功了。
SESSION1 >commit; Commit complete. SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/'; Table altered. SESSION2 >select * from stream; ID IDENTIFIER PIC_NO JPG_PATH ---------- --------------- ------ --------------- 1 18-0220-003 1 /home/oracle/ 2 18-0221-003 1 /home/oracle/ 3 18-0221-003 2 /home/oracle/ 4 18-0221-003 3 /home/oracle/ 5 18-0223-005 1 /home/oracle/ 6 18-0223-005 2 /home/oracle/ 7 18-0223-005 3 /home/oracle/ 8 18-0223-005 4 /home/oracle/ 9 18-0223-005 5 /home/oracle/ 10 18-0223-005 6 /home/oracle/ 10 rows selected.