oracle 12C dump数据块信息

dump数据块信息：

SQL> CREATE TABLESPACE tbs1 DATAFILE '/data1/oradata/ora1/tps101.dbf' SIZE 100M uniform size 1M;
SQL> create table ma.t64(id number(5),name varchar2(10)) tablespace tbs1;
SQL> insert into ma.t64 values(1,'a1');
SQL> commit;
SQL> select extent_id,file_id,block_id,blocks from dba_extents where owner='MA' and segment_name='T64' order by extent_id;

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          7        128        128

SQL> alter system dump datafile 7 block 128;

进入trace文件存储路径：

cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace

排序trace文件：

 ls -lrt

查看trace文件：

$ vim ora1_ora_2514.trc

*** 2019-04-12 08:40:56.714
Start dump data blocks tsn: 10 file#:7 minblk 128 maxblk 128
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=10 rdba=29360256
BH (0x96fdbfd8) file#: 7 rdba: 0x01c00080 (7/128) class: 8 ba: 0x96c68000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 93621 objn: 93621 tsn: [0/10] afn: 7 hint: f
  hash: [0xd1716bb8,0xd1716bb8] lru: [0x96fdc200,0x96fdbf80]
  ckptq: [NULL] fileq: [NULL]
  objq: [0xc2011130,0x96fdc228] objaq: [0x96fdc238,0x96fdbe78]
  st: XCURRENT md: NULL fpin: 'ktspfwh6: ktspffbmb' fscn: 0x0.41bade tch: 1
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [3]
Block dump from disk:
Encrypted block <10, 29360256> content will not be dumped. Dumping header only.    //数据块已加密
buffer tsn: 10 rdba: 0x01c00080 (7/128)
scn: 0x0.41bae5 seq: 0x03 flg: 0x04 tail: 0xbae52003
frmt: 0x02 chkval: 0xfbbd type: 0x20=FIRST LEVEL BITMAP BLOCK
End dump data blocks tsn: 10 file#: 7 minblk 128 maxblk 128

此时dump出来的数据块信息不足，信息里面有说明数据块内容被加密了无法查看。修改一个隐藏参数即可。

alter session set "_sga_clear_dump"=true;