oracle block資料塊結構續(一)

/**********檢視254條記錄以上的資料塊**********/
SQL> select  dbms_rowid.rowid_block_number(rowid),count(*) from t_block group by dbms_rowid.rowid_block_number(rowid) having count(*)>250;
DBMS_ROWID.ROWID_BLOCK_NUMBER(   COUNT(*)
------------------------------ ----------
                         50398        484

/***********dump上述一個資料塊**************/
SQL> alter system dump datafile 5 block 50535;
 
System altered

/**********定位trc檔案******************/
SQL> select * from v$diag_info;
 
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Default Trace File                                               d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_17324.trc
11 rows selected

/***********未刪除此資料塊前的dump*************/
Start dump data blocks tsn: 5 file#:5 minblk 50535 maxblk 50535
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5, rdba=21022055
BH (0x000007FF41FBC528) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF41918000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28
  dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF607493B8,0x000007FF607493B8] lru: [0x000007FF41FBC740,0x000007FF41FBC4E0]
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF41FBC2C8,0x000007FF41FBC788] fileq: [0x000007FF41FBC2D8,0x000007FF41FBC798] objq: [0x000007FF41FBC768,0x000007FF41FBC508]
  st: XCURRENT md: NULL tch: 3
  flags: buffer_dirty redo_since_read
  LRBA: [0xc2.283c.0] LSCN: [0x0.5b1fe9] HSCN: [0x0.5b1ff8] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 5 rdba: 0x0140c567 (5/50535)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x6327 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x000000001B978200 to 0x000000001B97A200
01B978200 0000A200 0140C567 00000000 05010000  [....g.@.........]
01B978210 00006327 00000000 00000000 00000000  ['c..............]
01B978220 00000000 00000000 00000000 00000000  [................]
        Repeat 508 times
01B97A1F0 00000000 00000000 00000000 00000001  [................]
Dump of memory from 0x000000001B978214 to 0x000000001B97A1FC
01B978210          00000000 00000000 00000000      [............]
01B978220 00000000 00000000 00000000 00000000  [................]
        Repeat 508 times
01B97A1F0 00000000 00000000 00000000           [............]   
End dump data blocks tsn: 5 file#: 5 minblk 50535 maxblk 50535

 

SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=50535;
 
484 rows deleted
 
SQL> alter system dump datafile 5 block 50535;
 
System altered
 

/***********刪除資料塊後的dump********/
Start dump data blocks tsn: 5 file#:5 minblk 50535 maxblk 50535
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5, rdba=21022055
BH (0x000007FF41FA4208) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF4168C000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28
  dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41FBC5D8,0x000007FF607493B8] lru: [0x000007FF43F98620,0x000007FF41FA41C0]
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF41FBC2C8,0x000007FF41FBC788] fileq: [0x000007FF41FBC2D8,0x000007FF41FBC798] objq: [0x000007FF41FBC768,0x000007FF41FBC508]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xc2.283c.0] LSCN: [0x0.5b1fe9] HSCN: [0x0.5b2176] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41FBC528) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF41918000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28
  dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF607493B8,0x000007FF41FA42B8] lru: [0x000007FF607C46F0,0x000007FF43F7BF50]
  lru-flags: moved_to_tail
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 2
  cr: [scn: 0x0.5b2174],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.5b2174],[sfl: 0x0],[lc: 0x0.5b1ff8]
  flags: redo_since_read
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 5 rdba: 0x0140c567 (5/50535)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 ----發現即便修改了資料塊，資料塊的scn沒有變化
frmt: 0x02 chkval: 0x6327 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x000000001B978200 to 0x000000001B97A200
01B978200 0000A200 0140C567 00000000 05010000  [....g.@.........]
01B978210 00006327 00000000 00000000 00000000  ['c..............]
01B978220 00000000 00000000 00000000 00000000  [................]
        Repeat 508 times
01B97A1F0 00000000 00000000 00000000 00000001  [................]
Dump of memory from 0x000000001B978214 to 0x000000001B97A1FC
01B978210          00000000 00000000 00000000      [............]
01B978220 00000000 00000000 00000000 00000000  [................]
        Repeat 508 times
01B97A1F0 00000000 00000000 00000000           [............]   
End dump data blocks tsn: 5 file#: 5 minblk 50535 maxblk 50535

 

SQL> --提交commit看下block scn是否發生變化
SQL> commit;
 
Commit complete
 
SQL> alter system dump datafile 5 block 50535;
 
System altered

buffer tsn: 5 rdba: 0x0140c567 (5/50535)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 --提交commit後的scn沒有發生變化

SQL> --發生檢查點我們看下是否發生變化
SQL> alter system checkpoint;
 
System altered
 
SQL> alter system dump datafile 5 block 50535;
 
System altered

scn: 0x0000.005b2211 seq: 0x01 flg: 0x06 tail: 0x22110601 --發生檢查點後資料塊的scn確實發生了變化

小結：1,block scn是指發生檢查點後已經寫入到資料檔案中的scn
      2,如果未發生檢查點，block scn一直是不變化的
     
     

/*********測試下對同一個資料塊變化多次scn及seq如何變化的，試著找到一點兒知識***********/
SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=49475 and rownum=1;--僅刪除資料塊的一條記錄
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL>  alter system dump datafile 5 block 49475;
 
System altered
 
SQL> select * from v$diag_info;
 
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Default Trace File                                               d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_17324.trc
11 rows selected     

/********未更新前的trace dump*********/     
scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601

scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601

scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601 --更新某個資料塊300條記錄後block scn沒有變化

小結：1,oracle11g r2上，對於某個資料塊不發生檢查點，更新這資料塊上面的所有表記錄，block scn不會發生變化
      2,seq我沒發現有什麼變化，一直是01
     
     
scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601 --檢查點前的某塊scn

/****************作操作但不提交commit*****************/
SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=50300 and rownum=1;
 
1 row deleted

/****************發生檢查點*************************/
SQL> alter system checkpoint;
 
System altered

/*********未提交commit發生檢查點後****************/
scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601 --發生檢查點後scn沒有變化   

/*******提交************/
SQL> commit;
 
Commit complete

 

/*********提交檢查點scn發生變化**********/

小結：1,必須commit加上檢查點 block scn才會發生變化