undo一致性读

SQL> create table test as select * from dba_objects where rownum<4;

Table created.

SQL> select rowid from test;

ROWID
------------------
AAAMjDAABAAAOuKAAA
AAAMjDAABAAAOuKAAB
AAAMjDAABAAAOuKAAC

更新一条数据：
SQL> update test set object_id=11 where object_id=20;

1 row updated.

SQL> select object_id from test;

 OBJECT_ID
----------
        11
        44
        28
SQL>  select rowid from test;

ROWID
------------------
AAAMjDAABAAAOuKAAA
AAAMjDAABAAAOuKAAB
AAAMjDAABAAAOuKAAC

----与未更新前rowid相同，说明数据的读取的物理地址可能是一样的

根据rowid取dba

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block from test;

       FNO      BLOCK
---------- ----------
         1      60298
         1      60298
         1      60298

SQL> alter system dump datafile 1 block 60298;

System altered.

数据块：

Start dump data blocks tsn: 0 file#: 1 minblk 60298 maxblk 60298 
buffer tsn: 0 rdba: 0x0040eb8a (1/60298)
scn: 0x0000.000a8909 seq: 0x01 flg: 0x00 tail: 0x89090601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CE96600 to 0x0CE98600
CE96600 0000A206 0040EB8A 000A8909 00010000  [......@.........]
CE96610 00000000 00000001 0000C8C3 000A8850  [............P...]
CE96620 00000000 00020003 00000000 0000FFFF  [................]
CE96630 00000000 00000000 00000000 00008000  [................]
CE96640 000A8850 0019000A 000000DC 00800727  [P...........'...]
CE96650 003400F0 00000001 00000000 00000000  [..4.............]
CE96660 00000000 00000000 00000000 00000000  [................]
CE96670 00000000 00030100 0018FFFF 1E911EA9  [................]
CE96680 00001E91 1F3E0003 1EA91EF2 00000000  [......>.........]
CE96690 00000000 00000000 00000000 00000000  [................]
        Repeat 487 times
CE98510 00000000 00000000 00000000 0D002C00  [.............,..]
CE98520 53595303 4E4F4304 C102FF24 1DC1021D  [.SYS.CON$.......]
CE98530 42415405 7807454C 141E0669 7807110B  [.TABLE.xi......x]
CE98540 141E0669 32130C29 2D353030 332D3630  [i...)..2005-06-3]
CE98550 39313A30 3A30313A 56053631 44494C41  [0:19:10:16.VALID]
CE98560 4E014E01 002C4E01 5953030D 5F490753  [.N.N.N,...SYS.I_]
CE98570 52455355 C102FF31 2DC1022D 444E4905  [USER1...-..-.IND]
CE98580 78075845 141E0669 7807110B 141E0669  [EX.xi......xi...]
CE98590 3213110B 2D353030 332D3630 39313A30  [...2005-06-30:19]
CE985A0 3A30313A 56053631 44494C41 4E014E01  [:10:16.VALID.N.N]
CE985B0 022C4E01 5953030D 43490553 FF244C4F  [.N,...SYS.ICOL$.]
CE985C0 020CC102 540503C1 454C4241 06697807  [.......TABLE.xi.]
CE985D0 110B141E 06697807 1F1D141E 30303213  [.....xi......200]
CE985E0 36302D35 3A30332D 313A3931 36313A30  [5-06-30:19:10:16]
CE985F0 4C415605 4E014449 4E014E01 89090601  [.VALID.N.N.N....]
Block header dump:  0x0040eb8a
 Object id on Block? Y
 seg/obj: 0xc8c3  csc: 0x00.a8850  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000a8850
0x02   0x000a.019.000000dc  0x00800727.00f0.34  ----    1  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0xce96674
===============
tsiz: 0x1f88
hsiz: 0x18
pbl: 0x0ce96674
bdba: 0x0040eb8a
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1ea9
avsp=0x1e91
tosp=0x1e91
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f3e
0x14:pri[1]     offs=0x1ef2
0x16:pri[2]     offs=0x1ea9
block_row_dump:
tab 0, row 0, @0x1f3e                                                      -----共三行数据，row0，row1，row2
tl: 74 fb: --H-FL-- lb: 0x2  cc: 13       -------lb 非0，说明此行有锁
col  0: [ 3]  53 59 53                                                           13行字段，col0，。。。col12
col  1: [ 5]  49 43 4f 4c 24
col  2: *NULL*
col  3: [ 2]  c1 0c
col  4: [ 2]  c1 03
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 69 06 1e 14 0b 11
col  7: [ 7]  78 69 06 1e 14 1d 1f
col  8: [19]  32 30 30 35 2d 30 36 2d 33 30 3a 31 39 3a 31 30 3a 31 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 1, @0x1ef2
tl: 76 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: *NULL*
col  3: [ 2]  c1 2d
col  4: [ 2]  c1 2d
col  5: [ 5]  49 4e 44 45 58
col  6: [ 7]  78 69 06 1e 14 0b 11
col  7: [ 7]  78 69 06 1e 14 0b 11
col  8: [19]  32 30 30 35 2d 30 36 2d 33 30 3a 31 39 3a 31 30 3a 31 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 2, @0x1ea9
tl: 73 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [ 4]  43 4f 4e 24
col  2: *NULL*
col  3: [ 2]  c1 1d
col  4: [ 2]  c1 1d
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 69 06 1e 14 0b 11
col  7: [ 7]  78 69 06 1e 14 29 0c
col  8: [19]  32 30 30 35 2d 30 36 2d 33 30 3a 31 39 3a 31 30 3a 31 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 60298 maxblk 60298

 

更新的object_id 是第一行，第四列 col  4: [ 2]  c1 03

SQL> select to_number('0c','xx') from dual;

TO_NUMBER('0C','XX')
--------------------
                  12

12-1=11 ,说明update后的数据写进了数据块（此数据块可能在内存中，要确定是否写进了磁盘可以看 dump datafile ‘/u01/xxxx.dbf' block 60298)

oracle维护一致性度，将update前的数据放在哪了呢？

lb: 0x2  说明占用itl 2号事物槽--  一个事物要修改数据块，必须要获得一个ITL

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x02   0x000a.019.000000dc  0x00800727.00f0.34  ----    1  fsc 0x0000.00000000

uba - undo block address  ---0x00800727.00f0.34   00800727--数据块   34--记录数

Flag ----   四个横线 表示有事物正在修改数据块

lck  1     锁定一行

SQL> select to_number('00800727','xxxxxxxxx') from dual;

TO_NUMBER('00800727','XXXXXXXXX')
---------------------------------
                          8390439

 

SQL> select dbms_utility.data_block_address_file(8390439) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8390439)
---------------------------------------------
                                            2

SQL> select dbms_utility.data_block_address_block(8390439) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8390439)
----------------------------------------------
                                          1831

SQL> alter system dump datafile 2 block 1831;

记录表：
UNDO BLK: 
xid: 0x000a.01d.000000dc  seq: 0xf0  cnt: 0x36  irb: 0x36  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70     0x02 0x1f18     0x03 0x1ec4     0x04 0x1e18     0x05 0x1d6c    
0x06 0x1cf0     0x07 0x1b2c     0x08 0x1ac0     0x09 0x1a6c     0x0a 0x1a10    
0x0b 0x19bc     0x0c 0x1950     0x0d 0x18cc     0x0e 0x185c     0x0f 0x17dc    
0x10 0x1708     0x11 0x16b4     0x12 0x1608     0x13 0x1548     0x14 0x14cc    
0x15 0x1448     0x16 0x1404     0x17 0x1358     0x18 0x12a8     0x19 0x124c    
0x1a 0x1208     0x1b 0x116c     0x1c 0x10bc     0x1d 0x1058     0x1e 0x0fbc    
0x1f 0x0f08     0x20 0x0ec4     0x21 0x0e28     0x22 0x0d78     0x23 0x0cf0    
0x24 0x0cac     0x25 0x0c10     0x26 0x0b60     0x27 0x0b04     0x28 0x0a68    
0x29 0x09b4     0x2a 0x0960     0x2b 0x08c4     0x2c 0x07f0     0x2d 0x076c    
0x2e 0x06c0     0x2f 0x05fc     0x30 0x0564     0x31 0x0480     0x32 0x0418    
0x33 0x03c0     0x34 0x0324     0x35 0x0278     0x36 0x00c0   

*-----------------------------
* Rec #0x34  slt: 0x19  objn: 51395(0x0000c8c3)  objd: 51395  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: 0x00800727.00f0.31 ctl max scn: 0x0000.000a83de prv tx scn: 0x0000.000a83e8
txn start scn: scn: 0x0000.000a88e9 logon user: 0
 prev brb: 8390433 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 10
ncol: 13 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040eb8a  hdba: 0x0040eb89
itli: 2  ispac: 0  maxfr: 4863
vect = 11
col  3: [ 2]  c1 15
 
*-----------------------------

col  3: [ 2]  c1 15
 

SQL> select to_number('15','xx') from dual;

TO_NUMBER('15','XX')
--------------------
                  21

21-1 正是update前object_id的值

在事物没有完成时，v$transaction也可以到UBA

select * from v$transaction