[20190125]MSSM表空间块ITL的LCK.txt

[20190125]MSSM表空间块ITL的LCK.txt

--//一般数据块的ITL的Lck记录的是影响的记录数,昨天测试使用系统表空间的数据块,发现第1次delete时记录的LCK记录的数量比实际多1.

--//系统表空间使用mssm,而普通用户的表空间建立的是assm,测试看看是否这个原因导致的问题.

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

CREATE TABLESPACE mssm DATAFILE 

  '/mnt/ramdisk/book/mssm01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

LOGGING

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;

--//使用SEGMENT SPACE MANAGEMENT MANUAL.

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

Table created.

SCOTT@book> Select rowid,t.* from t;

ROWID                      ID NAME

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

AAAWPxAAHAAAACBAAA          1 Test

AAAWPxAAHAAAACBAAB          2 Test

AAAWPxAAHAAAACBAAC          3 Test

AAAWPxAAHAAAACBAAD          4 Test

SCOTT@book> @ rowid AAAWPxAAHAAAACBAAA

    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT

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

     91121          7        129          0  0x1C00081           7,129                alter system dump datafile 7 block 129 ;

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

1 row deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system flush buffer_cache;

System altered.

SYS@book> @ bh 7 129

HLADDR           DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME

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

0000000084D14E60       7    129     1 data block free    0          0          0          0          0          0 000000006E35C000 T

0000000084D14E60       7    129     1 data block free    0          0          0          0          0          0 000000006E36C000 T

SCOTT@book> alter system dump datafile 7 block 129;

System altered.

--//检查转储可以发现:

Block header dump:  0x01c00081

 Object id on Block? Y

 seg/obj: 0x163f1  csc: 0x03.1774c9a7  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.1774c9a7

0x02   0x000a.01f.00005839  0x00c002a7.10da.13  --U-    2  fsc 0x0009.1774ca16

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

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

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

--//看来如果表空间是mssm,可能出现这样的情况,为什么就不知道了,是bug吗?

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

1 row deleted.

SCOTT@book> commit;

Commit complete.

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> alter system dump datafile 7 block 129;

System altered.

--//检查转储可以发现:

Block header dump:  0x01c00081

 Object id on Block? Y

 seg/obj: 0x163f1  csc: 0x03.1774c9a7  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.1774c9a7

0x02   0x000a.01f.00005839  0x00c002a7.10da.13  --U-    2  fsc 0x0009.1774ca16

0x03   0x000a.015.0000585d  0x00c002b3.10da.24  --U-    1  fsc 0x0009.1774cd68

bdba: 0x01c00081

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

--//再重复测试,删除4条记录.

SCOTT@book> drop table t purge ;

Table dropped.

SCOTT@book> create table t tablespace mssm as select rownum id,'b123' name from dual connect by level<=4;

Table created.

SCOTT@book> delete from t ;

4 rows deleted.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> alter system dump datafile 7 block 129;

System altered.

--//检查转储可以发现:

Block header dump:  0x01c00081

 Object id on Block? Y

 seg/obj: 0x163f4  csc: 0x03.1774cfc3  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.1774cfc3

0x02   0x000a.015.0000585e  0x00c002b4.10da.1f  --U-    5  fsc 0x0024.1774cfdd

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

--//看来使用mssm的表空间都有这样的情况,发现第一次delete时LCK第一次数量比实际多1次.为什么就不知道了?

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

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