[20190124]系统表空间块ITL的LCK.txt

[20190124]系统表空间块ITL的LCK.txt

--//一般数据块的ITL的Lck记录的是影响的记录数,但是如果是系统表空间的数据块呢?

1.环境:

SYSTEM@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

SYSTEM@book> create table t as select rownum id,'Test' name from dual connect by level<=4;

Table created.

SYSTEM@book> select rowid,t.* from t;

ROWID                      ID NAME

------------------ ---------- --------------------

AAAWPnAABAAAAnpAAA          1 Test

AAAWPnAABAAAAnpAAB          2 Test

AAAWPnAABAAAAnpAAC          3 Test

AAAWPnAABAAAAnpAAD          4 Test

SYSTEM@book> @ rowid AAAWPnAABAAAAnpAAA

    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

     91111          1       2537          0   0x4009E9           1,2537               alter system dump datafile 1 block 2537

SYSTEM@book> delete from t where id in 1;

1 row deleted.

SYSTEM@book> commit ;

Commit complete.

SYSTEM@book> alter system flush buffer_cache;

System altered.

SYSTEM@book> alter system dump datafile 1 block 2537;

System altered.

Block header dump:  0x004009e9

 Object id on Block? Y

 seg/obj: 0x163e7  csc: 0x03.17748be1  itc: 3  flg: O  typ: 1 - DATA

     fsl: 2  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0003.17748be1

0x02   0x0003.01c.00000735  0x00c005a9.0429.35  --U-    2  fsc 0x0009.17748c3c

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

--//我明明删除1条记录,而LCK记录的数量是2,为什么?

SYSTEM@book> delete from t where id in 2;

1 row deleted.

SYSTEM@book> commit ;

Commit complete.

SYSTEM@book> alter system flush buffer_cache;

System altered.

SYSTEM@book> alter system dump datafile 1 block 2537;

System altered.

Block header dump:  0x004009e9

 Object id on Block? Y

 seg/obj: 0x163e7  csc: 0x03.17748be1  itc: 3  flg: O  typ: 1 - DATA

     fsl: 2  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0003.17748be1

0x02   0x0003.01c.00000735  0x00c005a9.0429.35  --U-    2  fsc 0x0009.17748c3c

0x03   0x000a.017.00005831  0x00c001a7.10d6.11  --U-    1  fsc 0x0009.17748d2f

--//这次对了,为什么第1次会出现这样的情况呢?

--//如果重复测试,我删除4条记录.

SYSTEM@book> create table t as select rownum id,'Test' name from dual connect by level<=4;

Table created.

SYSTEM@book> delete from t ;

4 rows deleted.

SYSTEM@book> commit ;

Commit complete.

SYSTEM@book> alter system flush buffer_cache;

System altered.

SYSTEM@book> alter system dump datafile 1 block 2537;

System altered.

Block header dump:  0x004009e9

 Object id on Block? Y

 seg/obj: 0x163ec  csc: 0x03.1774915d  itc: 3  flg: O  typ: 1 - DATA

     fsl: 2  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0003.1774915d

0x02   0x0002.01c.0000073d  0x00c004fd.0288.2f  --U-    5  fsc 0x0024.1774916f

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

--//我做了多次测试,仅仅发现delete时LCK第一次数量比实际多1次.为什么?

--//如果用户的表空间就不存在这样的问题.那位解析看看.不理解.

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

转载于:http://blog.itpub.net/267265/viewspace-2564695/