oracle undo事物内部结构研究

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Feb 20 17:59:27 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
1
事物开始
SQL> update test set id=2;
1 row updated.
获取事物回滚段和回滚数据文件编号和block号
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 42 1606 25962 2 54
SQL> select usn,name from v$rollname where usn=9;
USN NAME
---------- ------------------------------
9 _SYSSMU9$
dump 回滚段
SQL> alter system dump undo header '_SYSSMU9$';
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num
0x2a 10 0x80 0x0646 0x0047 0x0000.00313f0e 0x0080656a 0x0000.000.00000000 0x00000001 0x00000000
10代表为活动事物 dba 地址为 0x0080656a 通过转换 位于datafile 2 block 25962 上
也可通过v$transaction 表XIDSQN,UBABLK 查出
dump 回滚里面的数据块内容
SQL> alter system dump datafile 2 block 25962;
System altered.
uba: 0x0080656a.00cf.35 ctl max scn: 0x0000.0030c0b8 prv tx scn: 0x0000.0030c0c0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.00a.000004e3 uba: 0x008016c9.0098.3a
flg: C--- lkc: 0 scn: 0x0000.00313ed5
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0140000f hdba: 0x0140000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [ 2] c1 02 --[color=#cc3333]这里保留是的前镜像值为1
查找数据文件所在文件号和块号
SQL> select output_rowid(rowid) from test;
OUTPUT_ROWID(ROWID)
--------------------------------------------------------------------------------
Object# is :6767
Relative_fno is :5
Block number is :15
Row number is :0
dump 文件号和块号
SQL> alter system dump datafile 5 block 15;
System altered.
SQL>
Start dump data blocks tsn: 5 file#: 5 minblk 15 maxblk 15
buffer tsn: 5 rdba: 0x0140000f (5/15)
scn: 0x0000.00313f0e seq: 0x01 flg: 0x00 tail: 0x3f0e0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0140000f
Object id on Block? Y
seg/obj: 0x1a6f csc: 0x00.313f0e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400009 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.007.00000397 0x00801902.007e.1a C--- 0 scn 0x0000.00313f06
0x02 0x0009.02a.00000646 0x0080656a.00cf.36 ---- 1 fsc 0x0000.00000000
xid 为事物槽
[color=#990000]0x0009.02a.00000646
ITL事务槽--Interested Transaction List(ITL)
ITL内容包括:
xid---Transaction ID
Uba---Undo Block Address
Lck---Lock Status

xid=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap
我们看到itl2上存在活动事务.
xid=[color=#990000]0x0009.02a.0000
指向9号回滚段.Slot号为42,Wrap#为[color=#990000]0646,正是我们dump回滚段看到的那个事务.ITL事务槽--Interested Transaction List(ITL)
ITL内容包括:
xid---Transaction ID
Uba---Undo Block Address
Lck---Lock Status

xid=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap
UBA 为[color=#990000]0x0080656a和 undo 回滚段的 dba [color=#990000]0x0080656a 一致

data_block_dump,data header at 0x5a11064
===============
tsiz: 0x1f98
hsiz: 0x1e
pbl: 0x05a11064
bdba: 0x0140000f
76543210
flag=--------
ntab=1
nrow=6
frre=1
fsbo=0x1e
fseo=0x1f56
avsp=0x1f71
tosp=0x1f71
0xe:pti[0] nrow=6 offs=0
0x12:pri[0] offs=0x1f56
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=-1
block_row_dump:
tab 0, row 0, @0x1f56
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03 --[color=#990000]新更新的值为2
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 15 maxblk 15
更新一个事物的流程
1.分配一个回滚段
2.在回滚段事务表中分配一个事务槽
3.分配undo block
4.更新数据块上的ITL事务槽
5.把前镜像记录在undo block内
6.更改数据块的内容