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

append與Highwater測試

2013-11-13 22:12:25  來源: Oracle 
創建測試表

   

  SQL> create table test_block as select * from dba_objects where = ;

  

  表已創建

  

  SQL> select OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS FREELISTS
      from dba_segments
     where segment_name = TEST_BLOCK;

  

  SEGMENT_NAME  HEADER_FILE HEADER_BLOCK  BLOCKS   EXTENTS

TEST_BLOCK                                      

  

  SQL> select EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
      from dba_extents
     where segment_name = TEST_BLOCK;

  

  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

                                                    

  

  SQL> insert into test_block select * from dba_objects where rownum < ;

  

  已創建

  

  SQL> select dbms_rowidrowid_relative_fno(rowid) file#
           dbms_rowidrowid_block_number(rowid) block#
      from test_block;

  

  FILE#     BLOCK#

               
               
               
               
               
               
               
               
               
               

         

  

  已選擇

   

   


數據插入到了第那麼被稱為 HEADER_BLOCK 的第塊是做什麼的呢?
 

  SQL> alter system dump datafile block ;

  

  系統已更改

  

  SQL> select pvalue||\||iinstance_name||_ora_||pspid||trc trace_path
    from v$process p v$instance i v$parameter p
    where paddr = (
    select paddr from v$session
    where sid = ( select sid from v$mystat where rownum = )
    )
    and pname=user_dump_dest;

  

  TRACE_PATH

D:\oracle\admin\ora\udump\orasid_ora_trc

   

   


我們看看塊中的信息
 

  *** ::
Start dump data blocks tsn: file#: minblk maxblk
buffer tsn: rdba: xcc (/)
scn: xbfaff seq: x flg: x tail: xaff
frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED
  Extent Control Header
 
  Extent Header:: spare:       spare:       #extents:       #blocks:     
                  last map  x  #maps:       offset:  
      Highwater::  xcc  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
 
   xcc  length:     
 
  nfl = nfb = typ = nxf = ccnt =
  SEG LST:: flg: USED   lhd: xcc ltl: xcc
End dump data blocks tsn: file#: minblk maxblk

   


 


  從 type 中可以看到第一個塊是數據段的頭信息我們看一下這裡的高水位標識(Highwater)
 

  SQL> select to_number(c xxxxxxxxxx) Highwater
      from dual;

  

  HIGHWATER

      


 
  現在高水位是在我再插入一條數據看看

   

  SQL> insert into test_block select * from dba_objects where rownum = ;

   

  已創建

   

  SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;

   

  BLOCK#

  

  

  

  

  

  

  

  

  

  

  

  

   

  已選擇

   

  SQL> rollback;

   

  回退已完成

   

  SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;

   

  已創建

   

  SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;

  select dbms_rowidrowid_block_number(rowid) block# from test_block

  *

  ERROR 位於第 行:

  ORA: 無法在並行模式下修改之後讀/修改對象

   

  SQL> alter system dump datafile block ;

   

  系統已更改

   

    此時塊信息如下 

   


  *** ::

  *** SESSION ID:() ::

  Start dump data blocks tsn: file#: minblk maxblk

  buffer tsn: rdba: xcc (/)

  scn: xbfaff seq: x flg: x tail: xaff

  frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED

  Extent Control Header

  

  Extent Header:: spare:       spare:       #extents:       #blocks:     

  last map  x  #maps:       offset:  

  Highwater::  xcc  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

  

  xcc  length:     

  

  nfl = nfb = typ = nxf = ccnt =

  SEG LST:: flg: USED   lhd: xcc ltl: xcc

  End dump data blocks tsn: file#: minblk maxblk

   

    

    append 一條數據後在提交前高水位並沒有發生變化但此時所插入的數據已經保存在第塊了此時第塊信息如下


   

  *** ::

  Start dump data blocks tsn: file#: minblk maxblk

  buffer tsn: rdba: xcc (/)

  scn: xbfc seq: x flg: x tail: xc

  frmt: x chkval: x type: x=trans data

  Block header dump:  xcc

  Object id on Block? Y

  seg/obj: xcfd  csc: xbfc  itc:   flg:   typ: DATA

  fsl:   fnx: x ver: x

  

  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

  x   x  x        fsc x

  x   x  x        fsc x

  x   x  x        fsc x

  

  data_block_dumpdata header at x

  ===============

  tsiz: xf

  hsiz: x

  pbl: x

  bdba: xcc

  

  flag=

  ntab=

  nrow=

  frre=

  fsbo=x

  fseo=xf

  avsp=xf

  tosp=xf

  xe:pti[] nrow=   offs=

  x:pri[]     offs=xf

  block_row_dump:

  tab row @xf

  tl: fb: HFL lb: x  cc:

  col  : [

  col  : []

  f f c e b f e e

  col  : *NULL*

  col  : [ ]  c

  col  : *NULL*

  col  : [a c

  col  : [ b f d

  col  : [ b f d

  col  : [ d d a a a

  col  : [ c

  col : [ e

  col : [ e

  col : [ e

  end_of_block_dump

  End dump data blocks tsn: file#: minblk maxblk

   

   我們繼續剛才的操作提交數據看此時數據在數據塊中的分布

   

  

  SQL> commit;

   

  提交完成

   

  SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;

   

  BLOCK#

  

  

  

  

  

  

  

  

  

  

  

  

  

   

  已選擇


 
  


看看此時的高水位

   

  SQL> alter system dump datafile block ;

   

  系統已更改

   

  塊的內容如下

   

  *** ::

  Start dump data blocks tsn: file#: minblk maxblk

  buffer tsn: rdba: xcc (/)

  scn: xbfcbf seq: x flg: x tail: xcbf

  frmt: x chkval: xe type: x=DATA SEGMENT HEADER UNLIMITED

  Extent Control Header

  

  Extent Header:: spare:       spare:       #extents:       #blocks:     

  last map  x  #maps:       offset:  

  Highwater::  xcc  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

  

  xcc  length:     

  

  nfl = nfb = typ = nxf = ccnt =

  SEG LST:: flg: USED   lhd: xcc ltl: xcc

  End dump data blocks tsn: file#: minblk maxblk

    

   

  

  SQL> select to_number(c xxx) from dual;

   

  TO_NUMBER(CXXX)

  

  

   

  提交後高水位移動到了第

   

   


使用直接路徑插入時會將數據插入到高水位以上的數據塊中不會掃描 freelist 中的空閒塊此時我再正常插入一條數據呢?應該插入到 因為此時 塊還沒有裝滿測試一下

   

  SQL> insert into test_block select * from dba_objects where rownum = ;

   

  已創建

   

  SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;

   

  BLOCK#

  

  

  

  

  

  

  

  

  

  

  

  

  

  

   

  已選擇

  SQL> rollback;

  回退已完成

   

  如果直接路徑插入後再回滾呢?高水位會發生什麼樣的變化?

   

  

  

  SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;

   

  已創建

   

  SQL> rollback;

   

  回退已完成

   

  SQL> alter system dump datafile block ;

   

  系統已更改


 

   

  *** ::

  Start dump data blocks tsn: file#: minblk maxblk

  buffer tsn: rdba: xcc (/)

  scn: xbfcbf seq: x flg: x tail: xcbf

  frmt: x chkval: xe type: x=DATA SEGMENT HEADER UNLIMITED

  Extent Control Header

  

  Extent Header:: spare:       spare:       #extents:       #blocks:     

  last map  x  #maps:       offset:  

  Highwater::  xcc  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

  

  xcc  length:     

  

  nfl = nfb = typ = nxf = ccnt =

  SEG LST:: flg: USED   lhd: xcc ltl: xcc

  End dump data blocks tsn: file#: minblk maxblk


 
 


  高水位沒有發生變化仍然在第而 append 一條數據後提交高水位會再移動到第
 

  

  SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;

   

  已創建

   

  SQL> commit;

   

  提交完成

   

  SQL> alter system dump datafile block ;

   

  系統已更改

   

   

  *** ::

  Start dump data blocks tsn: file#: minblk maxblk

  buffer tsn: rdba: xcc (/)

  scn: xbff seq: x flg: x tail: xf

  frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED

  Extent Control Header

  

  Extent Header:: spare:       spare:      #extents:       #blocks:     

  last map  x  #maps:       offset:  

  Highwater::  xcc  ext#:       blk#:       ext size:     

  #blocks in seg hdrs freelists:     

  #blocks below:     

  mapblk  x  offset:     

  Disk Lock:: Locked by xid:  xed

  Map Header:: next  x  #extents:     obj#:   flag: x

  Extent Map

  

  xcc  length:     

  

  nfl = nfb = typ = nxf = ccnt =

  SEG LST:: flg: USED   lhd: xcc ltl: xcc

  End dump data blocks tsn: file#: minblk maxblk

   


看看此時表 test_block 的數據分布

   

  SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;

   

  BLOCK#

  

  

  

  

  

  

  

  

  

  

  

  

  

  

   

  已選擇

   


小結
append 數據時會從 Highwater 之後的數據塊開始直接插入數據
append 數據後在提交之前不會升高 Highwater 的位置提交後才升高 Highwater


From:http://tw.wingwit.com/Article/program/Oracle/201311/18464.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.