[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,我的测试没有问题.