dump buffer cache

1.基础内容:
ALTER SESSION SET EVENTS 'immediate trace name buffers level n';
    n取值意义:
    1 只转储buffer header. 
    2 在level 1的基础上再转储数据块头。 
    3 在level 2的基础上再转储数据块内容。 
    4 转储buffer header和hash chain. 
    5 在level 1的基础上再转储数据块头和hash chain. 
    6 在level 2的基础上再转储数据块内容和hash chain. 
    8 转储buffer header和hash chain以及users/waiters链表。 
    9 在level 1的基础上再转储数据块头、hash chain以及users/waiters链表。 
    10 在level 2的基础上再转储数据块内容、hash chain以及users/waiters链表

2.刷新buffer cache
    alter system flush buffer_cache;

3.导出数据块的内容
    #确定表所对应的object_id
    select object_id from dba_objects where object_name='T01';        
    object_id
    -------------
    51780

4.将数据读入到buffer cache
    select * from t01;

5.dump出buffer cache
    ALTER SESSION SET EVENTS 'immediate trace name buffers level 3';
    当前的trace 文件位置:
    > oradebug setmypid;.
    > oradebug tracefile_name;


6.通过object_id 在dump文件中寻找表对应的块
    BH (000007FF087EECC8) file#: 1 rdba: 0x0040ed92 (1/60818) class: 1 ba: 000007FF08636000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
      dbwrid: 0 obj: 51780 objn: 51780 tsn: 0 afn: 1
      hash: [9bf53d8,feee108] lru: [97e7a88,57f7108]
      ckptq: [NULL] fileq: [NULL] objq: [97e7af8,e3d6dd8]
      st: XCURRENT md: NULL tch: 4
      flags: only_sequential_access
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040ed92 (1/60818)
      scn: 0x0000.000a213e seq: 0x04 flg: 0x06 tail: 0x213e0604
      frmt: 0x02 chkval: 0xf9a7 type: 0x06=trans data
    Hex dump of block: st=0, typ_found=1    

        .......
    data_block_dump,data header at 0x44d005c
    ===============
    tsiz: 0x1fa0
    hsiz: 0x1a
    pbl: 0x044d005c
    bdba: 0x0040ed92
         76543210
    flag=--------
    ntab=1
    nrow=4
    frre=3
    fsbo=0x1a
    fseo=0x1e42
    avsp=0x1f09
    tosp=0x1f09
    0xe:pti[0]    nrow=4    offs=0
    0x12:pri[0]    offs=0x1eb2
    0x14:pri[1]    offs=0x1ea6
    0x16:pri[2]    offs=0x1e42
    0x18:pri[3]    sfll=-1
    block_row_dump:
    tab 0, row 0, @0x1eb2
    tl: 13 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 6]  6f 72 61 63 6c 65
    tab 0, row 1, @0x1ea6
    tl: 12 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 5]  6d 79 73 71 6c
    tab 0, row 2, @0x1e42
    tl: 100 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [93]
     6d 79 73 71 6c 6f 72 61 63 6c 65 6d 79 73 71 6c 6f 72 61 63 6c 65 6d 79 73
     71 6c 6f 72 61 63 6c 65 6d 79 73 71 6c 6f 72 61 63 6c 65 6d 79 73 71 6c 6f
     72 61 63 6c 65 6d 79 73 71 6c 6f 72 61 63 6c 65 6d 79 73 71 6c 6f 72 61 63
     6c 65 6d 79 73 71 6c 6f 72 61 63 6c 65 6d 79 73 71 6c


相关字段简要说明:
|-------obj:51780 表示object_id
|
|-------class表示该buffer header所对应的数据块的类型,具体的值与含义的对应为:
|    1=data block;
|    2=sort block;
|    3=save undo block;
|    4=segment header;
|    5=save undo header;
|    6=free list;
|    7=extent map;
|    8=1st level bmb;
|    9=2nd level bmb;
|    10=3rd level bmb;
|    11=bitmap block;
|    12=bitmap index block;
|    13=unused;
|    14=undo header;
|    15=undo block。
|
|-------rdba: 0x0040ed92 (1/60818)表示该对象所对应的数据块在磁盘数据文件上的地址为 1号文件,60818块
|    #确定表所在文件及数据块
|    select 
|        rowid,
|        dbms_rowid.rowid_relative_fno(rowid) REL_FNO,--File id
|        dbms_rowid.rowid_block_number(rowid) BLOCK_NO,--Block number
|        dbms_rowid.rowid_row_number(rowid)   ROW_NO,
|        id
|    from t01;
|
|    ROWID                 REL_FNO   BLOCK_NO     ROW_NO         ID
|    ------------------ ---------- ---------- ---------- ----------
|    AAAMpEAABAAAO2SAAA          1      60818          0          0
|    AAAMpEAABAAAO2SAAB          1      60818          1          1
|    AAAMpEAABAAAO2SAAC          1      60818          2          1

参考blog:http://blog.csdn.net/orion61/article/details/8515340

原文地址:https://www.cnblogs.com/polestar/p/5839231.html