http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo002.htm

http://www.dbtan.com/2009/12/undo-deep-research.html

  update操作未提交,select操作如何查询update之前的数据
 

--创建测试表
create table t_undo(a int);
insert into t_undo values(1);
commit;

--update前,开启select会话
--10046的trace(开启12级，才能看到更细化的内容)
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=69926 p='TABLE ACCESS FULL T_UNDO (cr=7 pr=0 pw=0 time=0 us cost=2 size=0 card=82)'

--update且未提交,开启select会话
--10046的trace发现如下内容,新增如下一行内容，且cr增加了（cr为一性性读）
WAIT #5: nam='db file sequential read' ela= 203277 file#=10 block#=276619 blocks=1 obj#=69926 tim=8416122476
STAT #5 id=1 cnt=1 pid=0 pos=1 bj=69926 p='TABLE ACCESS FULL T_UNDO (cr=9 pr=1 pw=0 time=0 us cost=3 size=13 card=1)'

--单块读的文件和块号是什么
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_UNDO';
 
SEGMENT_NAME                                                                     HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
T_UNDO                                                                                    10       276618
 
SQL> select object_name from dba_objects where object_id=69926;
 
OBJECT_NAME
--------------------------------------------------------------------------------
T_UNDO

---说明oracle先读取表的数据块
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t_undo;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                            10                         276619
                           
--我们dump这个数据块
alter system dump datafile 10 block   276619;   

---itl介绍如下：
http://space.itpub.net/9240380/viewspace-756561

--trace文件如下
Block header dump:  0x0284388b
 Object id on Block? Y
 seg/obj: 0x11126  csc: 0x00.8580f2  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2843888 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.008.0000009f  0x00c0a929.013b.0f  C---    0  scn 0x0000.00843b74  flag为c表已提交即无活动事务
0x02   0x0005.017.00000844  0x00c04173.046e.1e  ----    1  fsc 0x0000.00000000 ：flag为---表未提交;lck为行锁数量,1即产生一个行级锁,uba即前镜像的地址,oracle根据uba去构造update之前的数据
bdba: 0x0284388b
data_block_dump,data header at 0x1a5d8264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x1a5d8264
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f92
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f92
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 0b --注意：这里是最新的记录数据，不是前镜像之前的1,而是uddate未提交的10;由select a,dump(a,16),dump(10,16) from t_undo可知
end_of_block_dump
End dump data blocks tsn: 8 file#: 10 minblk 276619 maxblk 276619                     

--逐一分析

SQL> select xid,ubafil,ubablk,ubasqn,ubarec from v$transaction;
 
XID                  UBAFIL     UBABLK     UBASQN     UBAREC
---------------- ---------- ---------- ---------- ----------
0500170044080000          3      16755       1134         30
--转换上述为十六进制刚好与之前dump t_undo表的数据块的itl uba一致：0x00c04173.046e.1e

--ubablk对应itl的uba第一组成部分0x00c04173的后半部分；而0x00c不知是什么?有待研究
SQL> select to_char(16755,'xxxxxxxx') from dual;
 
TO_CHAR(16755,'XXXXXXXX')
-------------------------
     4173

--ubasqn对应itl的uba第二组成部分046e 
SQL> select to_char(1134,'xxxxxxxx') from dual;
 
TO_CHAR(1134,'XXXXXXXX')
------------------------
      46e
--ubarec对应itl的uba第三组成部分1e     
SQL> select to_char(30,'xxxxxxxx') from dual;
 
TO_CHAR(30,'XXXXXXXX')
----------------------
       1e      
      
---我们继续,继续找到了update前镜像的地址,我们直接dump
--注意：先把itl的uba第一部分转换为10进制
SQL> select to_number('00c04173','XXXXXXXXXXXXXXX') from dual
  2  ;
 
TO_NUMBER('00C04173','XXXXXXXX
------------------------------
                      12599667
--然后再把上述的值用dbms_utility转换为撤消表空间的文件号及数据块号,最后再dump
SQL> select dbms_utility.data_block_address_file(12599667), dbms_utility.data_block_address_block(12599667) from dual;
 
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             3                          16755
 

dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
  hash: [0x000007FF0CEE2478,0x000007FF0CEE2478] lru: [0x000007FF03BF5C00,0x000007FF037DC5E0]
  lru-flags: hot_buffer
scn: 0x0000.0084ab3f seq: 0x01 flg: 0x04 tail: 0xab3f0201
frmt: 0x02 chkval: 0x689e type: 0x02=KTU UNDO BLOCK
      
********************************************************************************
********************************************************************************
--撤消块的撤消记录总条目
UNDO BLK: 
xid: 0x0005.017.00000844  seq: 0x46e cnt: 0x1e  irb: 0x1e  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1fa8     0x02 0x1f68     0x03 0x1f28     0x04 0x1ee8     0x05 0x1ea8    
0x06 0x1e68     0x07 0x1e28     0x08 0x1de8     0x09 0x1da8     0x0a 0x1d68    
0x0b 0x1d28     0x0c 0x1ce8     0x0d 0x1ca8     0x0e 0x1c68     0x0f 0x1c28    
0x10 0x1be8     0x11 0x1ba8     0x12 0x1b68     0x13 0x1b28     0x14 0x1ae8    
0x15 0x1aa8     0x16 0x1a68     0x17 0x1a28     0x18 0x19e8     0x19 0x19a8    
0x1a 0x1968     0x1b 0x18b8     0x1c 0x1854     0x1d 0x17f0     0x1e 0x1750    
 
*-----------------------------
---如下为一个具体的撤消条目明细撤消记录总条目的一个条目,而且这个撤消条目号与itl的uba的第三部分一样，
---所以说itl即通过这个在undo block中快速定位到是哪个撤消条目
--如下的rec :0x1e对应上面的
*-----------------------------
* Rec #0x1e  slt: 0x17  objn: 69926(0x00011126)  objd: 69926  tblspc: 8(0x00000008)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c04121.046e.6e ctl max scn: 0x0000.00844530 prv tx scn: 0x0000.008445b7
txn start scn: scn: 0x0000.0084ab1a logon user: 61
 prev brb: 12615867 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0284388b  hdba: 0x0284388a
itli: 2  ispac: 0  maxfr: 4858
vect = 0
col  0: [ 2]  c1 02  ---这就是t_undo表的前镜像,select to_number('c102','xxxxx') from dual;就是update之前的1