熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

關於回滾機制的一些測試

2013-11-13 22:12:36  來源: Oracle 

  SQL> connect test/test@test
  已連接
  SQL> create table test (a number);
  
  表已創建
  
  SQL> insert into test values();
  
  已創建
  SQL> select segment_nameheader_fileheader_block from dba_segments where segmen
  t_name like TEST;
  
  SEGMENT_NAME  HEADER_FILE     HEADER_BLOCK
  TEST                   
  SQL> alter system dump datafile block ;
  
  系統已更改
  DUMP出數據頭文件查看itl(interested transaction list)
  
  *** ::
  Start dump data blocks tsn: file#: minblk maxblk
  buffer tsn: rdba: xac (/)
  scn: xcd seq: x flg: x tail: xcd
  frmt: x chkval: x type: x=trans data
  
  Block header dump: xac
  Object id on Block? Y
  seg/obj: x csc: xcd itc:  flg: O typ: DATA
  fsl:  fnx: x ver: x
  
  Itl   Xid    Uba   Flag Lck   Scn/Fsc
  x  xid: x uba: xdc    fsc xc
  
  /*這裡可以看到xid= x(事務id)
  uba= xdc(undo block address)
  lck= (受影響的行數)
  根據Xid的結構得到
  x
   x – Undo Segment Number
    – Transaction Table Slot Number
   – Wrap
  根據uba的結構得到
   xdc
   xdc– Address of the last undo block used
    – Sequence
    – Last Entry in UNDO record map
  */
  data_block_dump
  ===============
  以下省略
  
  根據 x – Undo Segment Number
  SQL> select asegment_nameaheader_fileaheader_block from dba_segments adba
  _rollback_segs b where asegment_name=bsegment_name and bsegment_id=;
  
  SEGMENT_NAME  HEADER_FILE  HEADER_BLOCK
  RBS                  
  
  然後dump rbs頭查看trans table
  Start dump data blocks tsn: file#: minblk maxblk
  buffer tsn: rdba: xdc (/)
  scn: xcd seq: x flg: x tail: xcde
  frmt: x chkval: x type: xe=KTU UNDO HEADER W/UNLIMITED EXTENTS
  
  Extent Control Header
  
  Extent Header:: spare:    space:    #extents:    #blocks:
  last map x #maps:    offset:
  Highwater:: xdc ext#:    blk#:    ext size:
  #blocks in seg hdrs freelists:
  #blocks below:
  mapblk x offset:
  Unlocked
  Map Header:: next x #extents:   obj#:    flag: x
  Extent Map
  
  xdc length:
  xd length:
  x length:
  x length:
  x length:
  xc length:
  x length:
  xc length:
  
  TRN CTL:: seq: x chd: x ctl: x inc: x nfb: x
  mgc: x xts: x flg: x opt: (xffffffe)
  uba: xdc scn: x
  Version: x
  FREE BLOCK POOL::
  uba: x ext: x spc: x
  uba: x ext: x spc: x
  uba: x ext: x spc: x
  uba: x ext: x spc: x
  uba: x ext: x spc: x
  TRN TBL::
  
  index state cflags wrap#  uel   scn  dba   parentxid  nub
  
  x    x x x xcd xdc x x
  x    x x x x x x x
  以下省略
  根據從xid中得到的 – Transaction Table Slot Number
  去找到事務表中記載的undo塊的地址dba=xdc(也可從uba中直接得到)
  接下來我們來看一下undo頭的地址rdba: xdc (/)
  所以我們去dump 即undo頭+
  *** ::
  Start dump data blocks tsn: file#: minblk maxblk
  buffer tsn: rdba: xdc (/)
  scn: xcd seq: x flg: x tail: xcd
  frmt: x chkval: x type: x=KTU UNDO BLOCK
  
  ********************************************************************************
  UNDO BLK:
  xid: x seq: x  cnt: x  irb: x  icl: x  flg: x
  
  Rec Offset   Rec Offset   Rec Offset   Rec Offset   Rec Offset
  
  x xf   x xf   x xeb   x xe   x xde
  
  *
  * Rec #x slt: x objn: (x) objd:  tblspc: (x)
  *    Layer:  (Row)  opc:   rci x
  Undo type: Regular undo  Begin trans  Last buffer split: No
  Temp Object: No
  Tablespace Undo: No
  rdba: x
  *
  uba: x ctl max scn: x prv tx scn: x
  KDO undo record:
  KTB Redo
  op: x ver: x
  op: L itl: scn: xd uba: xfa
  flg: CU  lkc:    scn: xccf
  KDO Op code: DRP xtype: XA bdba: xac hdba: xab
  itli:  ispac:  maxfr:
  tabn: slot: (x)
  
  根據KDO Op code: DRP表明反操作是delete所以我們可以知道這就是剛才insert後在undo segment裡記載的信息
  
  我們知道當發生insert的時候undo segment裡僅記載了記錄的rowid下面我們把它找出來
  SQL> select rowid from test;
  
  ROWID
  
  AAAGSHAABAAAIoAAC
  Translate the value: AAAGSHAABAAAIoAAC
  
  Data Object number = AAAGSH
  File = AAB
  Block = AAAIo
  ROW = AAC
  
  然後根據公式轉換
  得到data object number=
  file=
  block=
  row=

From:http://tw.wingwit.com/Article/program/Oracle/201311/18476.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.