[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,我的測試沒有問題.