oracle undo insert实验

SQL> create table t1(id int,name varchar(4000));

Table created.

SQL> insert into t1 values(0,'abcdefgh');

1 row created.

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t1;    

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                   1                     5954

SQL> alter system dump datafile 1 block 5954;

System altered.

*** 2013-10-16 19:11:10.671
Start dump data blocks tsn: 0 file#: 1 minblk 5954 maxblk 5954
buffer tsn: 0 rdba: 0x00401742 (1/5954)
scn: 0x0000.0009d8b8 seq: 0x01 flg: 0x00 tail: 0xd8b80601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D921400 to 0x0D923400
D921400 0000A206 00401742 0009D8B8 00010000  [....B.@.........]
D921410 00000000 00000001 000024C8 0009D8B7  [.........$......]
D921420 00000000 00030002 00000000 00100004  [................]
D921430 0000009C 00803DE3 00010050 00000001  [.....=..P.......]
D921440 00000000 00000000 00000000 00000000  [................]
D921450 00000000 00000000 00000000 00010100  [................]
D921460 0014FFFF 1F7E1F92 00001F7E 1F920001  [......~.~.......]
D921470 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
D9233E0 00000000 00000000 00000000 012C0000  [..............,.]
D9233F0 08800102 64636261 68676665 D8B80601  [....abcdefgh....]
Block header dump:  0x00401742

Block header dump:  0x00401742
 Object id on Block? Y
 seg/obj: 0x24c8  csc: 0x00.9d8b7  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.010.0000009c  0x00803de3.0050.01  ----    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xd92145c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0d92145c
bdba: 0x00401742
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f92
avsp=0x1f7e
tosp=0x1f7e
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f92
block_row_dump:
tab 0, row 0, @0x1f92
tl: 14 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  80
col  1: [ 8]  61 62 63 64 65 66 67 68
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 5954 maxblk 5954

根据ITL事务槽0x01上可以看到此块有一个事务，xid指明undo段号，undo槽号，被覆盖次数，uba指明undo的地址和起始位置，我们根据uba来计算初uba的所在位置，然后dump出来，看看是怎么回事

计算过程:

uba= 0x00803de3.0050.01               2号文件，3de3块,1号rec           3de3=15843

alter system dump datafile 2 block 15843;

*** 2013-10-16 19:19:05.715
Start dump data blocks tsn: 1 file#: 2 minblk 15843 maxblk 15843
buffer tsn: 1 rdba: 0x00803de3 (2/15843)
scn: 0x0000.0009d8b8 seq: 0x01 flg: 0x04 tail: 0xd8b80201                        注释（可以从flg=0x04看出，该块是新建的）
frmt: 0x02 chkval: 0x743e type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D921400 to 0x0D923400
D921400 0000A202 00803DE3 0009D8B8 04010000  [.....=..........]
D921410 0000743E 00100004 0000009C 01010050  [>t..........P...]
D921420 1FE80000 00001F7C 00000000 00000000  [....|...........]
D921430 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
D923390 00480008 00140008 000024C8 000024C8  [..H......$...$..]
D9233A0 00000000 00000000 0010010B 00010408  [................]
D9233B0 00803DE2 00080050 0009C641 BFDF0000  [.=..P...A.......]
D9233C0 0009C649 00000000 0CBD2DE0 0009D8B8  [I........-......]
D9233D0 BFDF0000 00803DE0 00000000 00000000  [.....=..........]
D9233E0 00000103 00000016 00401742 00401741  [........B.@.A.@.]
D9233F0 010312FF 00000001 02000000 D8B80201  [................]

********************************************************************************
UNDO BLK:
xid: 0x0004.010.0000009c  seq: 0x50  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f7c

*-----------------------------
* Rec #0x1  slt: 0x10  objn: 9416(0x000024c8)  objd: 9416  tblspc: 0(0x00000000)
*       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: 0x00803de2.0050.08 ctl max scn: 0x0000.0009c641 prv tx scn: 0x0000.0009c649

txn start scn: scn: 0x0000.0009d8b8 logon user: 0
 prev brb: 8404448 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: 0x00401742  hdba: 0x00401741
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)

End dump data blocks tsn: 1 file#: 2 minblk 15843 maxblk 15843

可以看出，一开始rec 01没有任何信息,咱们试着做一次update,看看结果会有什么不同

SQL> alter t1 set name='ijklmn' where id=0;
alter t1 set name='ijklmn' where id=0
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> update t1 set name='ijklmn' where id=0;

1 row updated.

SQL> select * from t1;

    ID NAME
------ --------------------
     0 ijklmn

SQL> alter system dump datafile 1 block 5954;

System altered.

*** 2013-10-16 19:27:28.273
Start dump data blocks tsn: 0 file#: 1 minblk 5954 maxblk 5954
buffer tsn: 0 rdba: 0x00401742 (1/5954)
scn: 0x0000.0009da7d seq: 0x01 flg: 0x00 tail: 0xda7d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D921400 to 0x0D923400
D921400 0000A206 00401742 0009DA7D 00010000  [....B.@.}.......]
D921410 00000000 00000001 000024C8 0009D8B7  [.........$......]
D921420 00000000 00030002 00000000 00100004  [................]
D921430 0000009C 00803DE3 00020050 00020001  [.....=..P.......]
D921440 00000000 00000000 00000000 00000000  [................]
D921450 00000000 00000000 00000000 00010100  [................]
D921460 0014FFFF 1F7E1F86 00001F80 1F860001  [......~.........]
D921470 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
D9233E0 012C0000 06800102 6C6B6A69 012C6E6D  [..,.....ijklmn,.]
D9233F0 08800102 64636261 68676665 DA7D0601  [....abcdefgh..}.]
Block header dump:  0x00401742

 Object id on Block? Y
 seg/obj: 0x24c8  csc: 0x00.9d8b7  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.010.0000009c  0x00803de3.0050.02  ----    1  fsc 0x0002.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xd92145c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0d92145c
bdba: 0x00401742
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f86
avsp=0x1f7e
tosp=0x1f80
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f86
block_row_dump:
tab 0, row 0, @0x1f86
tl: 12 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  80
col  1: [ 6]  69 6a 6b 6c 6d 6e
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 5954 maxblk 5954

看见没有，相对原先的uba地址，最近dump出来的有新变化了，对比一下(原先的: 0x00803de3.0050.01)VS(后来的: 0x00803de3.0050.02)

其实uba字段最后一个是代表REC的起始，咱们就开始dump出来吧

SQL> alter system dump datafile 2 block 15843;

System altered.

*** 2013-10-16 19:32:25.997
Start dump data blocks tsn: 1 file#: 2 minblk 15843 maxblk 15843
buffer tsn: 1 rdba: 0x00803de3 (2/15843)
scn: 0x0000.0009da7d seq: 0x01 flg: 0x04 tail: 0xda7d0201
frmt: 0x02 chkval: 0x4f5a type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D921400 to 0x0D923400
D921400 0000A202 00803DE3 0009DA7D 04010000  [.....=..}.......]
D921410 00004F5A 00100004 0000009C 02020050  [ZO..........P...]
D921420 1FE80000 1F1C1F7C 00000000 00000000  [....|...........]
D921430 00000000 00000000 00000000 00000000  [................]
        Repeat 495 times
D923330 0018000C 001D0010 00080002 000024C8  [.............$..]
D923340 000024C8 00000000 00000000 0110010B  [.$..............]
D923350 009C0000 00000102 00000000 00803DE3  [.............=..]
D923360 00010050 00401742 00401741 010512FF  [P...B.@.A.@.....]
D923370 00000001 000A012C 01020000 00000002  [....,...........]
D923380 00000000 00000001 64636261 68676665  [........abcdefgh]
D923390 00480008 00140008 000024C8 000024C8  [..H......$...$..]
D9233A0 00000000 00000000 0010010B 00010408  [................]
D9233B0 00803DE2 00080050 0009C641 BFDF0000  [.=..P...A.......]
D9233C0 0009C649 00000000 0CBD2DE0 0009D8B8  [I........-......]
D9233D0 BFDF0000 00803DE0 00000000 00000000  [.....=..........]
D9233E0 00000103 00000016 00401742 00401741  [........B.@.A.@.]
D9233F0 010312FF 00000001 02000000 DA7D0201  [..............}.]

********************************************************************************
UNDO BLK:
xid: 0x0004.010.0000009c  seq: 0x50  cnt: 0x2   irb: 0x2   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f7c     0x02 0x1f1c

*-----------------------------
* Rec #0x1  slt: 0x10  objn: 9416(0x000024c8)  objd: 9416  tblspc: 0(0x00000000)
*       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: 0x00803de2.0050.08 ctl max scn: 0x0000.0009c641 prv tx scn: 0x0000.0009c649
txn start scn: scn: 0x0000.0009d8b8 logon user: 0
 prev brb: 8404448 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: 0x00401742  hdba: 0x00401741
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)

*-----------------------------
* Rec #0x2  slt: 0x10  objn: 9416(0x000024c8)  objd: 9416  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x01
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: 0x00803de3.0050.01
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00401742  hdba: 0x00401741
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 10
ncol: 2 nnew: 1 size: 2

col  1: [ 8]  61 62 63 64 65 66 67 68

End dump data blocks tsn: 1 file#: 2 minblk 15843 maxblk 15843

好吧，咱们从 REC2开始看，看它的col1信息,我这里就直接把这个信息转换成ASCII编码吧，abcdefgh,发现什么了吗，没错，这是我们第一次插入的数据。

再思考一下，那么REC为什么是空呢，首先因为要回滚，所以会记录一些以前的信息。

好吧，还有好多信息我还没探测，现在实验就到此为止吧。