[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次.为什么就不知道了?