ITPub博客

首页 > 数据库 > Oracle > [20190522]DISABLE TABLE LOCK.txt

[20190522]DISABLE TABLE LOCK.txt

原创 Oracle 作者:lfree 时间:2019-05-22 11:01:19 0 删除 编辑

[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;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2645161/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2435
  • 访问量
    6216329