[20190522]DISABLE TABLE LOCK.txt

[20190522]DISABLE TABLE LOCK.txt --//如果禁止table lock时,一些ddl操作会被禁止.但是我有点吃惊的是增加字段不受限制. --//通过测试说明问题. 1.环境: 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. 2.测试: SCOTT@book> truncate table t; truncate table t                * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T SCOTT@book> alter table t add v1 varchar2(10); Table altered. --//增加1列v1没有问题. SCOTT@book> @ desc t Name  Null?    Type ----- -------- ---------------------------- ID             NUMBER V1             VARCHAR2(10) SCOTT@book> alter table t drop column v1; alter table t drop column v1 * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T --//删除列不行. SCOTT@book> alter table t set unused column v1; alter table t set unused column v1 * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T 3.继续: SCOTT@book> alter table t enable table lock; Table altered. SCOTT@book> alter table t set unused column v1; Table altered. --//有时候感觉oracle设计的很奇怪,也就是增加1列不需要TM锁吗? 3.继续测试:即使有事务存在的情况下. --//session 1: SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------         30        535 42553                    DEDICATED 42554       26        245 alter system kill session '30,535' immediate; SCOTT@book> alter table t disable table lock; Table altered. --//session 2: SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------          1      10297 43346                    DEDICATED 43347       24        237 alter system kill session '1,10297' immediate; SCOTT@book> insert into t values (2); 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 ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------      1      10297 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655375     4583723                                           No --//session 1:   SCOTT@book> alter table t add v1 varchar2(10); Table altered. SCOTT@book> alter table t add v2 varchar2(10); Table altered. --//确实可以. --//session 2:   SCOTT@book> select * from t;         ID V1         V2 ---------- ---------- ----------          1          2 SCOTT@book> insert into t values (3); insert into t values (3)             * ERROR at line 1: ORA-00947: not enough values --//增加了字段. SCOTT@book> insert into t values (3,'a','b'); 1 row created. SCOTT@book> select * from t;         ID V1         V2 ---------- ---------- ----------          1          2          3 a          b 4.如果enable table lock,如果有会话事务没有提交,在别的会话增加一列一定会挂起: --//感觉oracle的设计还真奇怪.... --//session 1: SCOTT@book>  alter table t enable table lock; Table altered. --//session 2: SCOTT@book> insert into t values (4,'a','b'); 1 row created. --//session 1: SCOTT@book> alter table t add v3 varchar2(10); --//挂起!! 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 ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------      1      10297 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       42044813   0          SCOTT  TABLE      T                    No      1      10297 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655379     4583660                                           Yes     30        535 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  None       Share      655379     4583660                                           No    0000000085439E00     30        535 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       42044813   0          SCOTT  TABLE      T                    No    0000000085439E00     30        535 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       589849     212173                                            No    0000000085439E00 5.viewlock.sql脚本: $ cat viewlock.sql column sid format 99999 column username format a10 column osuser format a10 column machine format a10 column lock_type format a15 column mode_held format a10 column mode_requested format a10 column lock_id1 format a10 column lock_id2 format a10 column owner format a6 column object_type format a10 column object_name format a20 column block format a5 column lockwait format a20 SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,se.module,        DECODE (lk.TYPE, 'TX', 'TX Transaction', 'TM', 'TM DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,        DECODE (lk.lmode,                0, 'None',                1, 'Null',                2, 'Row-S (SS)',                3, 'Row-X (SX)',                4, 'Share',                5, 'S/Row-X (SSX)',                6, 'Exclusive',                TO_CHAR (lk.lmode)               ) mode_held,        DECODE (lk.request,                0, 'None',                1, 'Null',                2, 'Row-S (SS)',                3, 'Row-X (SX)',                4, 'Share',                5, 'S/Row-X (SSX)',                6, 'Exclusive',                TO_CHAR (lk.request)               ) mode_requested,        TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,        DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait   FROM v$lock lk, dba_objects ob, v$session se  WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+) order by 1;