[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次.為什麼?

--//如果使用者的表空間就不存在這樣的問題.那位解析看看.不理解.