十一、多行插入

1、创建实验环境

SQL>create table test1 (id number not null, object_name varchar2(20));
SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;

SQL>insert into test1 select rownum, object_name from dba_objects where rownum<4;
SQL>commit;

SQL>select dbms_flashback.get_system_change_number scn from dual;

SQL>alter system dump logfile
          '/home/xxxxx/oracle/oradata/xxxxx/redo03.log'
         scn min 56403000234
         scn max 56403000242;

2、trace 文件解析

CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0x01000044 OBJ:142805 SCN:0x000d.21e17bb1 SEQ:  1 OP:11.11
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0004.026.000046d9    uba: 0x00800ef3.4245.0d
KDO Op code: QMI row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000044  hdba: 0x01000043
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 lock: 1 nrow: 3
slot[0]: 0
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  43 4f 4e 24
slot[1]: 1
tl: 13 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 6]  49 5f 43 4f 4c 32
slot[2]: 2
tl: 14 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 7]  49 5f 55 53 45 52 23
--"OP: 11.11"和"KDO Op code: QMI" 表示插入多行数据
--插入三行数据加到DBA=01000044, slot# 0 1 2,
--nrow：number rows=3
--该操作undo的位置uba= 0x00800ef3.4245.0d(undo block address.sequence number.record number)
--交易XID=0x0004.026.000046d9(USN#.SLOT#.WRAP#)

CHANGE #11 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17b18 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0026 sqn: 0x000046d9 flg: 0x0012 siz: 116 fbi: 0
            uba: 0x00800ef3.4245.0d    pxid:  0x0000.000.00000000
--修改undo segment header信息,具体对应undo信息slot, sequence


CHANGE #12 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17bb1 SEQ:  1 OP:5.4
ktucm redo: slt: 0x0026 sqn: 0x000046d9 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800ef3.4245.0d ext: 2 spc: 6712 fbi: 0
--commit;

CHANGE #13 TYP:0 CLS:24 AFN:2 DBA:0x00800ef3 OBJ:4294967295 SCN:0x000d.21e17b17 SEQ:  3 OP:5.1
ktudb redo: siz: 116 spc: 6830 flg: 0x0012 seq: 0x4245 rec: 0x0d
            xid:  0x0004.026.000046d9
ktubl redo: slt: 38 rci: 0 opc: 11.1 objn: 142805 objd: 142805 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800ee5.4245.26
prev ctl max cmt scn:  0x000d.21e1685f  prev tx cmt scn:  0x000d.21e16879
txn start scn:  0x000d.21e17ba7  logon user: 55  prev brb: 8392427  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: QMD row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000044  hdba: 0x01000043
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 lock: 0 nrow: 3
slot[0]: 0
slot[1]: 1
slot[2]: 2
--修改undo数据块具体信息
--“KDO Op code: QMD” 多行删除，删除的block位置在bdba=0x01000044, slot 0 1 2


总结：

多行的插入所产生的redo log大小与插入一行所产生的redo log大小基本相同