十一、多行插入
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大小基本相同
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大小基本相同
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-609919/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-609919/