oracle實驗記錄 （dump undo）

SQL> select * from test;

TESTCOL
--------------------
bb
bb
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_nu
mber(rowid) blk#  from test;

     FILE#       BLK#
---------- ----------
         1      56802
         1      56802
SQL> update test set testcol='c';

已更新2行。

SQL> alter system dump datafile 1 block 56802;

系統已更改。

SQL>
Start dump data blocks tsn: 0 file#: 1 minblk 56802 maxblk 56802
buffer tsn: 0 rdba: 0x0040dde2 (1/56802)
scn: 0x0000.00414b6e seq: 0x02 flg: 0x00 tail: 0x4b6e0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x0040dde2
 Object id on Block? Y
 seg/obj: 0xd1fb  csc: 0x00.412b70  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.00f.00000bbf  0x0080315c.07f3.07  ----    2  fsc 0x0000.00000000
0x02   0x0004.02d.00000796  0x008003ca.0351.2b  C---    0  scn 0x0000.00409edf
 
data_block_dump,data header at 0x72a025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x072a025c
bdba: 0x0040dde2
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f4e
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f53
0x14:pri[1] offs=0x1f4e
block_row_dump:
tab 0, row 0, @0x1f53
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  63
tab 0, row 1, @0x1f4e
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  63~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~轉換過來為 C

end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 56802 maxblk 56802
SQL> select chr(to_number('63','xxxx')) from dual
  2  ;

CH
--
c

0x0080315c.07f3.07 ~~~~UBA在undo 中的 資訊

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('80315c','xxxx
xxx'));

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('80315c','xx
xxxxx'));

PL/SQL 過程已成功完成。

SQL> print file#

     FILE#
----------
         2

SQL> print blk#

      BLK#
----------
     12636

SQL>
SQL> alter system dump datafile 2 block 12636
  2  ;

系統已更改。          uba0x0080315c.07f3.07               中07為0x7(irb)回滾鏈末記錄號

* Rec #0x7  slt: 0x0f  objn: 53755(0x0000d1fb)  objd: 53755  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x06  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~rci
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x0080315c.07f3.06
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040dde2  hdba: 0x0040dde1
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 1
col  0: [ 2]  62 62~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~原來的值 但只是第一行的
 
End dump data blocks tsn: 1 file#: 2 minblk 12636 maxblk 12636

SQL> select chr(to_number('62','xxxx')) from dual~~~~~~~~~~~~~~~~~~~~~~~~~~~`它對應12636塊第1行
  2  ;

CH
--
b

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```
*-----------------------------
* Rec #0x6  slt: 0x0f  objn: 53755(0x0000d1fb)  objd: 53755  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x0080315c.07f3.03 ctl max scn: 0x0000.00410098 prv tx scn: 0x0000.004100dc
txn start scn: scn: 0x0000.00414b6e logon user: 0
 prev brb: 8401239 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
op: L  itl: xid:  0x0003.00f.00000bb7 uba: 0x0080312d.07f3.24
                      flg: C---    lkc:  0     scn: 0x0000.004081df
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040dde2  hdba: 0x0040dde1
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0~~~~~~~~~~~~~~~~~~~~~~~slot 0 第一行
ncol: 1 nnew: 1 size: 1
col  0: [ 2]  62 62
 
*-----------------------------
* Rec #0x7  slt: 0x0f  objn: 53755(0x0000d1fb)  objd: 53755  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x06  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x0080315c.07f3.06
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040dde2  hdba: 0x0040dde1
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0~~~~~~~~~~~~~~~~~~~~~~~slot 1第2行
ncol: 1 nnew: 1 size: 1
col  0: [ 2]  62 62
 
End dump data blocks tsn: 1 file#: 2 minblk 12636 maxblk 12636

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607605/，如需轉載，請註明出處，否則將追究法律責任。