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   --這裡保留是的前映象值為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 為事物槽

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=0x0009.02a.0000
指向9號回滾段.Slot號為42,Wrap#為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 為0x0080656a和 undo 回滾段的 dba 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  --新更新的值為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.更改資料塊的內容