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

oracle undo一

相關文章