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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-756705/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-756705/
1518
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
