Redo內部解析-Insert Single Row with Index（七）

十、插入一行記錄到有索引的表

1、建立實驗環境
SQL> create table test (id number, test varchar2(20));
SQL> create index test_idx on test(id);
SQL> col scn format 999999999990
SQL> select dbms_flashback.get_system_change_number scn from dual;
SQL> insert into test values( 1, 'test1');
SQL> commit;
SQL> select dbms_flashback.get_system_change_number scn from dual;

SQL> alter system dump logfile
          '/home/xxxxx/oracle/oradata/xxxxx/redo02.log'
         scn min 56402991291
         scn max 56402991298;

2、分析

以上實驗所產生的redo至少要包含以下幾個方面：
  
     1、記錄表test資料塊變化情況
     2、記錄該表在undo產生的變化情況
     3、記錄commit的資訊
     4、記錄索引資料塊變化情況
     5、記錄索引在undo產生變化的情況

3、trace檔案解析

CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0x01000026 OBJ:142797 SCN:0x000d.21e158c0 SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x000a.02a.00004d6f    uba: 0x00801d51.44df.01
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000026  hdba: 0x01000023
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 12
fb: --H-FL-- lb: 0x1  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 5]  74 65 73 74 31

--表test (dba_objects.object_id=142797)插入一行資料（IRP)。

CHANGE #11 TYP:0 CLS:35 AFN:2 DBA:0x00800099 OBJ:4294967295 SCN:0x000d.21e15825 SEQ:  1 OP:5.2
ktudh redo: slt: 0x002a sqn: 0x00004d6f flg: 0x000a siz: 108 fbi: 1
            uba: 0x00801d51.44df.01    pxid:  0x0000.000.00000000
--更新undo segment#的資訊

CHANGE #12 TYP:0 CLS: 1 AFN:4 DBA:0x0100002c OBJ:142798 SCN:0x000d.21e158b1 SEQ:  1 OP:10.2
index redo (kdxlin):  insert leaf row
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x000a.02a.00004d6f    uba: 0x00801d51.44df.02
REDO: SINGLE / -- / --
itl: 2, sno: 0, row size 14
insert key: (10):  02 c1 02 06 01 00 00 26 00 00

--OP:10.2 insert leaf row
--插入值解析： 02： Key 長度
               c1 02: Column Value
               06: rowid 長度
               01000026： 表的DBA
               00 00 : row #
              
          
CHANGE #13 TYP:0 CLS:35 AFN:2 DBA:0x00800099 OBJ:4294967295 SCN:0x000d.21e158c0 SEQ:  1 OP:5.4
ktucm redo: slt: 0x002a sqn: 0x00004d6f srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00801d51.44df.02 ext: 2 spc: 7938 fbi: 0
--commit;

CHANGE #14 TYP:1 CLS:36 AFN:2 DBA:0x00801d51 OBJ:4294967295 SCN:0x000d.21e158bd SEQ:  1 OP:5.1
ktudb redo: siz: 108 spc: 0 flg: 0x000a seq: 0x44df rec: 0x01
            xid:  0x000a.02a.00004d6f
ktubl redo: slt: 42 rci: 0 opc: 11.1 objn: 142797 objd: 142797 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00801d4e.44df.30
prev ctl max cmt scn:  0x000d.21e14779  prev tx cmt scn:  0x000d.21e1485b
txn start scn:  0x000d.21e158b7  logon user: 55  prev brb: 8396112  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000026  hdba: 0x01000023
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)

CHANGE #15 TYP:0 CLS:36 AFN:2 DBA:0x00801d51 OBJ:4294967295 SCN:0x000d.21e158c0 SEQ:  1 OP:5.1
ktudb redo: siz: 100 spc: 8040 flg: 0x0022 seq: 0x44df rec: 0x02
            xid:  0x000a.02a.00004d6f
ktubu redo: slt: 42 rci: 1 opc: 10.22 objn: 142798 objd: 142798 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x000d.21e158b0
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=2 indexid=0x100002b block=0x0100002c
(kdxlpu): purge leaf row
key :(10):  02 c1 02 06 01 00 00 26 00 00

--undo記錄了insert索引的key值。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-609911/，如需轉載，請註明出處，否則將追究法律責任。