不同insert操作產生的undo的測試
對於http://space.itpub.net/756652/viewspace-242408 這個以前的一個測試地址，有人會問，如果批量insert只有1條或者少數幾條undo，那麼如何回滾所有資料？下面給出些測試說明這個問題。
SYS.YUDONG>conn zwf/zwf
Connected.
1 row selected.
SYS.YUDONG>select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------
2203
1 row selected.
ZWF.YUDONG>create table t(id number);
Table created.
ZWF.YUDONG>select owner,segment_name,file_id,block_id from dba_extents where wner='ZWF' and segment_name='T';
OWNER SEGMENT_NAME FILE_ID BLOCK_ID
------------------------------ --------------------------------------------------------------------------------- ---------- ----------
ZWF T 20 3649
1 row selected.
session 1:
ZWF.YUDONG>insert into t values(1);
1 row created.
ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;
SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
---------------------------------------- ---------------------------------------- ----------
20 3650 1
1 row selected.
ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 26 34383 2 734 1565 23 1 1
1 row selected.
Dump file /dumps-01/databases/yudong/udump/yudong_ora_2203.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0.2/A11db
System name: Linux
Node name: db-tfusn2cn-a-r1.amazon.com
Release: 2.4.21-37a6smp
Version: #1 SMP Thu May 25 13:59:02 PDT 2006
Machine: i686
Instance name: yudong
Redo thread mounted by this instance: 1
Oracle process number: 37
Unix process pid: 2203, image: oracle@db-tfusn2cn-a-r1.amazon.com (TNS V1-V3)
*** 2008-04-21 12:28:57.856
*** ACTION NAME:() 2008-04-21 12:28:57.856
*** MODULE NAME:(SQL*Plus) 2008-04-21 12:28:57.856
*** SERVICE NAME:(SYS$USERS) 2008-04-21 12:28:57.856
*** SESSION ID:(590.318) 2008-04-21 12:28:57.856
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af2c8 seq: 0x04 flg: 0x00 tail: 0xf2c80604
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF2C8 00040000 [....B.....Z.....]
D2E4210 00000000 00000001 0001189D 165AF2C8 [..............Z.]
D2E4220 00000000 00030002 00000000 001A000A [................]
D2E4230 0000864F 008002DE 0017061D 00000001 [O...............]
D2E4240 00000000 00000000 00000000 00000000 [................]
D2E4250 00000000 00000000 00000000 00010100 [................]
D2E4260 0014FFFF 1F831F9A 00001F83 1F9A0001 [................]
D2E4270 00000FFF 00000000 00000000 00000000 [................]
D2E4280 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
D2E5230 00000000 00010000 00010001 00000001 [................]
D2E5240 00000000 00000001 0040000A 0040000A [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000 [................]
Repeat 249 times
D2E61F0 00000000 012C0000 02C10201 F2C80604 [......,.........]
Block header dump: 0x05000e42
Object id on Block? Y
seg/obj: 0x1189d csc: 0x00.165af2c8 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01a.0000864f 0x008002de.061d.17 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xd2e425c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0d2e425c
bdba: 0x05000e42
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
session 2:
ZWF.YUDONG>begin
for i in 1..10 loop
insert into t values(2);
end loop;
end;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;
SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
---------------------------------------- ---------------------------------------- ----------
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
20 3650 2
10 rows selected.
ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
8 6 33665 2 25033 1922 32 1 10
10 26 34383 2 734 1565 23 1 1
2 rows selected.
*** 2008-04-21 12:34:45.815
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af62b seq: 0x01 flg: 0x00 tail: 0xf62b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF62B 00010000 [....B...+.Z.....]
D2E4210 00000000 00000001 0001189D 165AF62B [............+.Z.]
D2E4220 00000000 00030002 00000000 001A000A [................]
D2E4230 0000864F 008002DE 0017061D 00000001 [O...............]
D2E4240 00000000 00060008 00008381 008061C9 [.............a..]
D2E4250 00200782 0000000A 00000000 000B0100 [.. .............]
D2E4260 0028FFFF 1F151F5E 00001F15 1F9A000B [..(.^...........]
D2E4270 1F8E1F94 1F821F88 1F761F7C 1F6A1F70 [........|.v.p.j.]
D2E4280 1F5E1F64 00000000 00000000 00000000 [d.^.............]
D2E4290 00000000 00000000 00000000 00000000 [................]
Repeat 249 times
D2E5230 00000000 00010000 00010001 00000001 [................]
D2E5240 00000000 00000001 0040000A 0040000A [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000 [................]
Repeat 245 times
D2E61B0 00000000 00000000 022C0000 03C10201 [..........,.....]
D2E61C0 0201022C 022C03C1 03C10201 0201022C [,.....,.....,...]
D2E61D0 022C03C1 03C10201 0201022C 022C03C1 [..,.....,.....,.]
D2E61E0 03C10201 0201022C 022C03C1 03C10201 [....,.....,.....]
D2E61F0 0201022C 012C03C1 02C10201 F62B0601 [,.....,.......+.]
Block header dump: 0x05000e42
Object id on Block? Y
seg/obj: 0x1189d csc: 0x00.165af62b itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01a.0000864f 0x008002de.061d.17 ---- 1 fsc 0x0000.00000000
0x02 0x0008.006.00008381 0x008061c9.0782.20 ---- 10 fsc 0x0000.00000000
data_block_dump,data header at 0xd2e425c
===============
tsiz: 0x1fa0
hsiz: 0x28
pbl: 0x0d2e425c
bdba: 0x05000e42
76543210
flag=--------
ntab=1
nrow=11
frre=-1
fsbo=0x28
fseo=0x1f5e
avsp=0x1f15
tosp=0x1f15
0xe:pti[0] nrow=11 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
0x16:pri[2] offs=0x1f8e
0x18:pri[3] offs=0x1f88
0x1a:pri[4] offs=0x1f82
0x1c:pri[5] offs=0x1f7c
0x1e:pri[6] offs=0x1f76
0x20:pri[7] offs=0x1f70
0x22:pri[8] offs=0x1f6a
0x24:pri[9] offs=0x1f64
0x26:pri[10] offs=0x1f5e
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f8e
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 3, @0x1f88
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 4, @0x1f82
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 5, @0x1f7c
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 6, @0x1f76
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 7, @0x1f70
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 8, @0x1f6a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 9, @0x1f64
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 10, @0x1f5e
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
session 3:
ZWF.YUDONG>insert into t select 3 from dual connect by rownum<=10;
10 rows created.
ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;
SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
---------------------------------------- ---------------------------------------- ----------
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
20 3650 3
10 rows selected.
ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3 47 110659 2 21319 7704 23 1 1
8 6 33665 2 25033 1922 32 1 10
10 26 34383 2 734 1565 23 1 1
3 rows selected.
*** 2008-04-21 12:36:52.655
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af7fa seq: 0x01 flg: 0x00 tail: 0xf7fa0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF7FA 00010000 [....B.....Z.....]
D2E4210 00000000 00000001 0001189D 165AF7FA [..............Z.]
D2E4220 00000000 00030003 00000000 001A000A [................]
D2E4230 0000864F 008002DE 0017061D 00000001 [O...............]
D2E4240 00000000 00060008 00008381 008061C9 [.............a..]
D2E4250 00200782 0000000A 00000000 002F0003 [.. .........../.]
D2E4260 0001B043 00805347 00171E18 0000000A [C...GS..........]
D2E4270 00000000 00150100 003CFFFF 1E8F1F0A [..........<.....>D2E4280 00001E8F 1F820015 1F761F7C 1F6A1F70 [........|.v.p.j.]
D2E4290 1F5E1F64 1F521F58 1F461F4C 1F101F0A [d.^.X.R.L.F.....]
D2E42A0 1F1C1F16 1F281F22 1F341F2E 1F401F3A [....".(...4.:.@.]
D2E42B0 00000000 00000000 00000000 00000000 [................]
Repeat 247 times
D2E5230 00000000 00010000 00010001 00000001 [................]
D2E5240 00000000 00000001 0040000A 0040000A [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000 [................]
Repeat 241 times
D2E6170 00000000 00000000 00000000 032C0000 [..............,.]
D2E6180 04C10201 0201032C 032C04C1 04C10201 [....,.....,.....]
D2E6190 0201032C 032C04C1 04C10201 0201032C [,.....,.....,...]
D2E61A0 032C04C1 04C10201 0201032C 032C04C1 [..,.....,.....,.]
D2E61B0 04C10201 0201032C 022C04C1 03C10201 [....,.....,.....]
D2E61C0 0201022C 022C03C1 03C10201 0201022C [,.....,.....,...]
D2E61D0 022C03C1 03C10201 0201022C 022C03C1 [..,.....,.....,.]
D2E61E0 03C10201 0201022C 022C03C1 03C10201 [....,.....,.....]
D2E61F0 0201022C 012C03C1 02C10201 F7FA0601 [,.....,.........]
Block header dump: 0x05000e42
Object id on Block? Y
seg/obj: 0x1189d csc: 0x00.165af7fa itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01a.0000864f 0x008002de.061d.17 ---- 1 fsc 0x0000.00000000
0x02 0x0008.006.00008381 0x008061c9.0782.20 ---- 10 fsc 0x0000.00000000
0x03 0x0003.02f.0001b043 0x00805347.1e18.17 ---- 10 fsc 0x0000.00000000
data_block_dump,data header at 0xd2e4274
===============
tsiz: 0x1f88
hsiz: 0x3c
pbl: 0x0d2e4274
bdba: 0x05000e42
76543210
flag=--------
ntab=1
nrow=21
frre=-1
fsbo=0x3c
fseo=0x1f0a
avsp=0x1e8f
tosp=0x1e8f
0xe:pti[0] nrow=21 offs=0
0x12:pri[0] offs=0x1f82
0x14:pri[1] offs=0x1f7c
0x16:pri[2] offs=0x1f76
0x18:pri[3] offs=0x1f70
0x1a:pri[4] offs=0x1f6a
0x1c:pri[5] offs=0x1f64
0x1e:pri[6] offs=0x1f5e
0x20:pri[7] offs=0x1f58
0x22:pri[8] offs=0x1f52
0x24:pri[9] offs=0x1f4c
0x26:pri[10] offs=0x1f46
0x28:pri[11] offs=0x1f0a
0x2a:pri[12] offs=0x1f10
0x2c:pri[13] offs=0x1f16
0x2e:pri[14] offs=0x1f1c
0x30:pri[15] offs=0x1f22
0x32:pri[16] offs=0x1f28
0x34:pri[17] offs=0x1f2e
0x36:pri[18] offs=0x1f34
0x38:pri[19] offs=0x1f3a
0x3a:pri[20] offs=0x1f40
block_row_dump:
tab 0, row 0, @0x1f82
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f7c
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f76
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 3, @0x1f70
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 4, @0x1f6a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 5, @0x1f64
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 6, @0x1f5e
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 7, @0x1f58
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 8, @0x1f52
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 9, @0x1f4c
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 10, @0x1f46
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 11, @0x1f0a
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 12, @0x1f10
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 13, @0x1f16
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 14, @0x1f1c
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 15, @0x1f22
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 16, @0x1f28
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 17, @0x1f2e
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 18, @0x1f34
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 19, @0x1f3a
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
tab 0, row 20, @0x1f40
tl: 6 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 2] c1 04
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
我們主要分析itl中對應的transaction 8 和 3 的undo record的差異:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01a.0000864f 0x008002de.061d.17 ---- 1 fsc 0x0000.00000000
0x02 0x0008.006.00008381 0x008061c9.0782.20 ---- 10 fsc 0x0000.00000000
0x03 0x0003.02f.0001b043 0x00805347.1e18.17 ---- 10 fsc 0x0000.00000000
ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3 47 110659 2 21319 7704 23 1 1
8 6 33665 2 25033 1922 32 1 10
10 26 34383 2 734 1565 23 1 1
3 rows selected.
ZWF.YUDONG>select * from v$rollname where usn in (3,8);
USN NAME
---------- ------------------------------
3 _SYSSMU3$
8 _SYSSMU8$
2 rows selected.
ZWF.YUDONG>alter system dump undo header '_SYSSMU3$';
System altered.
ZWF.YUDONG>alter system dump undo header '_SYSSMU8$';
System altered.
********************************************************************************
Undo Segment: _SYSSMU8$ (8)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 16 #blocks: 1807
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008061c9 ext#: 7 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 7
Unlocked
Map Header:: next 0x00000000 #extents: 16 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080007a length: 7
0x00803899 length: 8
0x00800689 length: 128
0x00805889 length: 128
0x00803489 length: 128
0x00801709 length: 128
0x00803a89 length: 128
0x00806189 length: 128
0x00804509 length: 128
0x00803b89 length: 128
0x00802c89 length: 128
0x00804709 length: 128
0x00802589 length: 128
0x00803e89 length: 128
0x00803f09 length: 128
0x00804b89 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1208693578
Extent Number:1 Commit Time: 1208694673
Extent Number:2 Commit Time: 1208712469
Extent Number:3 Commit Time: 1208730814
Extent Number:4 Commit Time: 1208743237
Extent Number:5 Commit Time: 1208761498
Extent Number:6 Commit Time: 1208776740
Extent Number:7 Commit Time: 1208608139
Extent Number:8 Commit Time: 1208625144
Extent Number:9 Commit Time: 1208641460
Extent Number:10 Commit Time: 1208658434
Extent Number:11 Commit Time: 1208667658
Extent Number:12 Commit Time: 1208667664
Extent Number:13 Commit Time: 1208667666
Extent Number:14 Commit Time: 1208678288
Extent Number:15 Commit Time: 1208692832
TRN CTL:: seq: 0x0782 chd: 0x002b ctl: 0x0027 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008061c9.0782.17 scn: 0x0000.165af28d
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0782.16 ext: 0x7 spc: 0x13b6
uba: 0x00000000.0782.28 ext: 0x7 spc: 0x10fe
uba: 0x00000000.0781.28 ext: 0x6 spc: 0x27e
uba: 0x00000000.01cb.22 ext: 0x7 spc: 0x123a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x837e 0x001c 0x0000.165af51f 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781120
0x01 9 0x00 0x8380 0x000b 0x0000.165af4f7 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781105
0x02 9 0x00 0x8382 0x0016 0x0000.165af2b1 0x008061c7 0x0000.000.00000000 0x00000001 0x00000000 1208780900
0x03 9 0x00 0x8381 0x002e 0x0000.165af398 0x008061c7 0x0000.000.00000000 0x00000001 0x00000000 1208780981
0x04 9 0x00 0x8381 0x000c 0x0000.165af4b4 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781080
0x05 9 0x00 0x8383 0x0014 0x0000.165af422 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781034
0x06 10 0x80 0x8381 0x0007 0x0000.165af607 0x008061c9 0x0000.000.00000000 0x00000001 0x00000000 0 --狀態為10的為當前活動事務
0x07 9 0x00 0x8382 0x0027 0x0000.165af5ee 0x008061c9 0x0000.000.00000000 0x00000001 0x00000000 1208781194
0x08 9 0x00 0x8383 0x0011 0x0000.165af4d4 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781094
。。。。。。
0x2e 9 0x00 0x8382 0x0013 0x0000.165af3a0 0x008061c7 0x0000.000.00000000 0x00000001 0x00000000 1208780984
0x2f 9 0x00 0x8380 0x0000 0x0000.165af515 0x008061c8 0x0000.000.00000000 0x00000001 0x00000000 1208781115
********************************************************************************
Undo Segment: _SYSSMU3$ (3)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 56 #blocks: 2607
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00805347 ext#: 55 blk#: 62 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 55
Unlocked
Map Header:: next 0x00000000 #extents: 56 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080002a length: 7
0x00800a89 length: 8
0x00801609 length: 128
0x00802509 length: 128
0x00802089 length: 128
0x00802209 length: 128
0x00804c89 length: 128
0x00800a09 length: 128
0x00804389 length: 128
0x00806209 length: 128
0x00805989 length: 128
0x00805a09 length: 128
0x00805c89 length: 128
0x00806309 length: 128
0x00800041 length: 8
0x00800051 length: 8
......
0x00801c91 length: 8
0x00801c99 length: 8
0x00801ca1 length: 8
0x00805289 length: 128
0x00800e09 length: 128
0x00802009 length: 128
0x00803a09 length: 128
0x00802c09 length: 128
0x00805309 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1208589051
Extent Number:1 Commit Time: 1208590410
Extent Number:2 Commit Time: 1208604784
。。。。。。
Extent Number:53 Commit Time: 1208752899
Extent Number:54 Commit Time: 1208768590
Extent Number:55 Commit Time: 1208768590
TRN CTL:: seq: 0x1e18 chd: 0x002d ctl: 0x001b inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00805347.1e18.17 scn: 0x0000.165af574
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.1e18.16 ext: 0x37 spc: 0x138e
uba: 0x00000000.1e17.02 ext: 0x36 spc: 0x1f06
uba: 0x00000000.1e18.07 ext: 0x37 spc: 0x1d00
uba: 0x00000000.1c21.0d ext: 0x3 spc: 0x1750
uba: 0x00000000.004f.51 ext: 0x4f spc: 0x474
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x1b04a 0x002e 0x0000.165af6a1 0x00805346 0x0000.000.00000000 0x00000001 0x00000000 1208781255
......
0x2d 9 0x00 0x1b044 0x0020 0x0000.165af58d 0x00805345 0x0000.000.00000000 0x00000001 0x00000000 1208781160
0x2e 9 0x00 0x1b042 0x0022 0x0000.165af6bf 0x00805346 0x0000.000.00000000 0x00000001 0x00000000 1208781265
0x2f 10 0x80 0x1b043 0x0037 0x0000.165af7e8 0x00805347 0x0000.000.00000000 0x00000001 0x00000000 0 --狀態為10的為當前活動事務
根據dba dump下undo的block:
ZWF.YUDONG>declare
2 v_file number;
3 v_block number;
4 begin
5 v_file := dbms_utility.data_block_address_file(to_number('008061c9','xxxxxxxx'));
6 v_block := dbms_utility.data_block_address_block(to_number('008061c9','xxxxxxxx'));
7 dbms_output.put_line('file id: '||v_file);
8 dbms_output.put_line('block id: '||v_block);
9 end;
10 /
file id: 2
block id: 25033
PL/SQL procedure successfully completed.
ZWF.YUDONG>declare
2 v_file number;
3 v_block number;
4 begin
5 v_file := dbms_utility.data_block_address_file(to_number('00805347','xxxxxxxx'));
6 v_block := dbms_utility.data_block_address_block(to_number('00805347','xxxxxxxx'));
7 dbms_output.put_line('file id: '||v_file);
8 dbms_output.put_line('block id: '||v_block);
9 end;
10 /
file id: 2
block id: 21319
PL/SQL procedure successfully completed.
ZWF.YUDONG>alter system dump datafile 2 block 25033;
System altered.
ZWF.YUDONG>alter system dump datafile 2 block 21319;
System altered.
*** 2008-04-21 12:56:09.821
Start dump data blocks tsn: 1 file#: 2 minblk 25033 maxblk 25033
buffer tsn: 1 rdba: 0x008061c9 (2/25033)
scn: 0x0000.165af607 seq: 0x0a flg: 0x00 tail: 0xf607020a
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
。。。。。。
********************************************************************************
UNDO BLK:
xid: 0x0008.006.00008381 seq: 0x782 cnt: 0x20 irb: 0x20 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f20 0x02 0x1e5c 0x03 0x1df8 0x04 0x1da4 0x05 0x1d08
0x06 0x1c9c 0x07 0x1ba4 0x08 0x1b3c 0x09 0x1ae4 0x0a 0x1a20
0x0b 0x195c 0x0c 0x1910 0x0d 0x18a4 0x0e 0x1844 0x0f 0x17d8
0x10 0x170c 0x11 0x16a4 0x12 0x164c 0x13 0x15e8 0x14 0x1594
0x15 0x14bc 0x16 0x13f4 0x17 0x1370 0x18 0x1314 0x19 0x12b8
0x1a 0x125c 0x1b 0x1200 0x1c 0x11a4 0x1d 0x1148 0x1e 0x10ec
0x1f 0x1090 0x20 0x1034
*-----------------------------
* Rec #0x17 slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008061c9.0782.16 ctl max scn: 0x0000.165af274 prv tx scn: 0x0000.165af28d
txn start scn: scn: 0x0000.165af607 logon user: 67
prev brb: 8413639 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: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1)
*-----------------------------
* Rec #0x18 slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x17
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.17
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2)
*-----------------------------
* Rec #0x19 slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x18
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.18
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3)
*-----------------------------
* Rec #0x1a slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x19
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.19
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4)
*-----------------------------
* Rec #0x1b slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1a
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1a
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 5(0x5)
*-----------------------------
* Rec #0x1c slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1b
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1b
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 6(0x6)
*-----------------------------
* Rec #0x1d slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1c
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1c
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 7(0x7)
*-----------------------------
* Rec #0x1e slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1d
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1d
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 8(0x8)
*-----------------------------
* Rec #0x1f slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1e
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1e
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 9(0x9)
*-----------------------------
* Rec #0x20 slt: 0x06 objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x1f
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008061c9.0782.1f
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x05000e42 hdba: 0x05000e41
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 10(0xa)
End dump data blocks tsn: 1 file#: 2 minblk 25033 maxblk 25033
*** 2008-04-21 12:55:57.552
Start dump data blocks tsn: 1 file#: 2 minblk 21319 maxblk 21319
buffer tsn: 1 rdba: 0x00805347 (2/21319)
scn: 0x0000.165af7e8 seq: 0x01 flg: 0x00 tail: 0xf7e80201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
。。。。。。
********************************************************************************
UNDO BLK:
xid: 0x0003.02f.0001b043 seq: 0x1e18 cnt: 0x17 irb: 0x17 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1ef0 0x02 0x1e88 0x03 0x1e30 0x04 0x1d6c 0x05 0x1ca4
0x06 0x1bac 0x07 0x1b44 0x08 0x1aec 0x09 0x1aa0 0x0a 0x1a34
0x0b 0x19d4 0x0c 0x1968 0x0d 0x189c 0x0e 0x1834 0x0f 0x17dc
0x10 0x1778 0x11 0x1724 0x12 0x164c 0x13 0x159c 0x14 0x1500
0x15 0x1494 0x16 0x13cc 0x17 0x1330
*-----------------------------
* Rec #0x17 slt: 0x2f objn: 71837(0x0001189d) objd: 71837 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00805347.1e18.16 ctl max scn: 0x0000.165af56c prv tx scn: 0x0000.165af574
txn start scn: scn: 0x0000.165af7e8 logon user: 67
prev brb: 8409925 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: 0x05000e42 hdba: 0x05000e41
itli: 3 ispac: 0 maxfr: 4863
tabn: 0 lock: 0 nrow: 10
slot[0]: 11
slot[1]: 12
slot[2]: 13
slot[3]: 14
slot[4]: 15
slot[5]: 16
slot[6]: 17
slot[7]: 18
slot[8]: 19
slot[9]: 20
End dump data blocks tsn: 1 file#: 2 minblk 21319 maxblk 21319
對於批量insert,oracle儲存在undo中為一個record或者少數幾個（如果資料跨越多個undoblock）,其中有多個slot槽儲存相應的資料；
而每個單獨的insert操作,oracle均在undo中有一個對應的record,所以insert from table,bulk insert等操作都會有比較好的效能。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-246351/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 測試APPEND INSERT是否產生UNDO資訊的過程APP
- oracle dml產生undo的區別小測試Oracle
- 哪種DML操作產生undo多
- mysql的DDL操作對業務產生影響測試MySql
- impdp操作產生大量UNDO的原因及解決方法
- oracle 11g不同會話產生的事務會使用相同的undo segment嗎--undo系列之一Oracle會話
- insert操作放在undo中的rowid怎麼來的！
- oracle redo日誌產生量測試及比較2_insertOracle Redo
- Oracle中undo 如何產生RedoOracle
- 軟體測試、自動化測試極容易產生的誤區
- Redis快速產生測試資料的三種方法Redis
- 【Mysql】兩條insert 語句產生的死鎖MySql
- 策劃入門（九）遊戲測試方案的產生(轉)遊戲
- 產生TX鎖等待不同情形的分析
- 操作生產環境的規範
- CTAS和insert append的一個測試APP
- 唯一索引操作可能產生的鎖索引
- vue專案打包配置多個測試環境與生產環境，用npm命令打出不同的資源包。VueNPM
- 我渴望的insert操作!
- 介面測試-使用mock生產隨機資料Mock隨機
- 介面測試-使用 mock 生產隨機資料Mock隨機
- 生產系統恢復到異機測試
- 表number列的資料插入insert小測試
- 線上非rman備份產生和普通操作產生的redo大小比較
- insert的不同場景效能比較
- 小白不懂就問：做介面測試產生的資料怎麼清除
- 測試部的技術如何在公司內外產生影響力
- 轉roger大師_計算sql語句產生的redo和undo大小SQL
- 載入不同位置hibernate包產生的問題
- c#中的insert操作C#
- oracle insert插入number(1)列的資料小測試Oracle
- 關於加快INSERT語句執行速度的測試
- LMT更新file header bitmap不產生redo和undo ?Header
- Delete,insert,update與undo的關係[轉載TOM文章]delete
- 測試DML 時產生歸檔日誌和閃回日誌的比
- SQLTest系列之INSERT語句測試SQL
- Git undo 操作Git
- oracle redo日誌產生量測試及比較1Oracle Redo