oracle實驗記錄 （dump undo3）

SQL> select * from test;

TESTCOL
--------------------
ed
ed

 

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>
SQL> update test set testcol='d';

已更新2行。

SQL> commit;

提交完成。

SQL> alter system dump datafile 1 block 56802;

系統已更改。

SQL> update test set testcol='de';

已更新2行。

SQL> commit;

提交完成。

SQL> alter system dump datafile 1 block 56802;

系統已更改。

SQL> update test set testcol='ded';

已更新2行。

SQL> alter system dump datafile 1 block 56802;

系統已更改。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL>
Object id on Block? Y
 seg/obj: 0xd1fb  csc: 0x00.427fca  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01a.0000025e  0x00800031.0175.2e  C---    0  scn 0x0000.00427fac
0x02   0x0007.028.0000025f  0x008008a2.02cd.41  --U-    2  fsc 0x0000.00428542
 
data_block_dump,data header at 0x648025c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x0648025c
bdba: 0x0040dde2
     76543210
flag=--------
ntab=1
nrow=4
frre=0
fsbo=0x1a
fseo=0x1eba
avsp=0x1f74
tosp=0x1f74
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=-1
0x16:pri[2] offs=0x1ebf
0x18:pri[3] offs=0x1eba
block_row_dump:
tab 0, row 2, @0x1ebf
tl: 5 fb: --H-FL-- lb: 0x2  cc: 1                           第一次dump
col  0: [ 1]  64
tab 0, row 3, @0x1eba
tl: 5 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 1]  64
end_of_block_dump
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Block header dump:  0x0040dde2
 Object id on Block? Y
 seg/obj: 0xd1fb  csc: 0x00.42854f  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.023.000002c3  0x008002ff.01b6.20  --U-    2  fsc 0x0000.00428550~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~第2次dump
0x02   0x0007.028.0000025f  0x008008a2.02cd.41  C---    0  scn 0x0000.00428542~~~~~~~~~~~~原來U變C了
 
data_block_dump,data header at 0x648025c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x0648025c
bdba: 0x0040dde2
     76543210
flag=--------
ntab=1
nrow=4
frre=0
fsbo=0x1a
fseo=0x1eae
avsp=0x1f74
tosp=0x1f74
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=-1
0x16:pri[2] offs=0x1eb4
0x18:pri[3] offs=0x1eae
block_row_dump:
tab 0, row 2, @0x1eb4
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  64 65
tab 0, row 3, @0x1eae
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  64 65
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 56802 maxblk 56802
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.023.000002c3  0x008002ff.01b6.20  C---    0  scn 0x0000.00428550
0x02   0x000a.02f.00000253  0x0080033f.03e1.16  ----    2  fsc 0x0000.00000000~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~第3次DUMP   0x008008a2.02cd.41 這條uba換出去了
 
data_block_dump,data header at 0x648025c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x0648025c
bdba: 0x0040dde2
     76543210
flag=--------
ntab=1
nrow=4
frre=0
fsbo=0x1a
fseo=0x1ea0
avsp=0x1f74
tosp=0x1f74
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=-1
0x16:pri[2] offs=0x1ea7
0x18:pri[3] offs=0x1ea0
block_row_dump:
tab 0, row 2, @0x1ea7
tl: 7 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 3]  64 65 64
tab 0, row 3, @0x1ea0
tl: 7 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 3]  64 65 64
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 56802 maxblk 56802

~~~~~~~~~~~~~~~~~~~~~~~~~~~`0x0080033f.03e1.16 ~~看看這條最新的uba~~~~~~~~~~~~~~~~~~~根據.16在第16條記錄

SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('80033f','xx
xxxxxx'));

PL/SQL 過程已成功完成。

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('80033f','xx
xxxxxx'));

PL/SQL 過程已成功完成。

SQL> print file#

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

SQL> print blk#

      BLK#
----------
       831
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~dump 2,831

* Rec #0x15  slt: 0x2f  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: 0x0080033f.03e1.12 ctl max scn: 0x0000.00427f25 prv tx scn: 0x0000.00427f2c
txn start scn: scn: 0x0000.00428554 logon user: 0
 prev brb: 8389435 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
op: L  itl: xid:  0x0007.028.0000025f uba: 0x008008a2.02cd.41~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~問題有思路了現在的塊了有被換出去的uba 0x008008a2.02cd.41
                      flg: C---    lkc:  0     scn: 0x0000.00428542
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040dde2  hdba: 0x0040dde1
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: -1
col  0: [ 2]  64 65
 
*-----------------------------
* Rec #0x16  slt: 0x2f  objn: 53755(0x0000d1fb)  objd: 53755  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x15  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~上一個位置~~~~~~~~~~~~~~~~
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: 0x0080033f.03e1.15
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040dde2  hdba: 0x0040dde1
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: -1
col  0: [ 2]  64 65
 
End dump data blocks tsn: 1 file#: 2 minblk 831 maxblk 831

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