[20190530]DISABLE TABLE LOCK(10g).txt

[20190530]DISABLE TABLE LOCK(10g).txt --//如果禁止table lock时,一些ddl操作会被禁止.但是我有点吃惊的是增加字段不受限制. --//链接:http://blog.itpub.net/267265/viewspace-2645161/=>[20190522]DISABLE TABLE LOCK.txt  --//昨天kerrycode给我反馈,10g和11g某个版本增加1列会遇到ORA-00069错误. --//在http://www.itpub.net/thread-2117203-1-1.html提问,失望竟然没人解答与测试. --//今天在10g下重复测试: 1.环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi SCOTT@test> create table t as select rownum id from dual ; Table created. SCOTT@test> alter table t disable table lock; Table altered. 2.测试: SCOTT@test> truncate table t; truncate table t                * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T SCOTT@test> alter table t add v1 varchar2(10); alter table t add v1 varchar2(10) * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T --//很明显10g在设置disable table lock的情况下,无法增加1列.在11g下重复看看. 3.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table t as select rownum id from dual ; Table created. SCOTT@book> alter table t disable table lock; Table altered. SCOTT@book> alter table t add v1 varchar2(10); Table altered. --//11.2.0.4下确实可以不是我的测试问题.并且在有事务的情况下一样可以增加字段. --//打开session 2: SCOTT@book> insert into t values (2,'a'); 1 row created. SCOTT@book> @ viewlock    SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- --------     44       4845 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655390     5246287                                  No --//打开session 1: SCOTT@book> alter table t add v2 varchar2(10); Table altered. SCOTT@book> @ viewlock    SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- --------     44       4845 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655390     5246287                                  No --//看来这个是一个bug在11.2.0.4,我的测试没有问题.