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