DUMP数据文件特定块到跟踪文件里

DUMP数据文件特定块到跟踪文件里

1.建立测试表
SQL> create table dump (id number,name varchar(20));
Table created.
SQL> insert into dump values (1000,'tomsh');
1 row created.
SQL> insert into dump values (1001,'dumpceshi');
1 row created.
SQL> commit;
Commit complete.
2.查询表在数据文件以及块号
select file_id, block_id from dba_extents
 where  segment_name = 'DUMP';
SQL> select file_id, block_id from dba_extents
 where  segment_name = 'DUMP';  2
   FILE_ID   BLOCK_ID
---------- ----------
         1      85992

SQL> col name format a50;
SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /opt/Oracle/db/oradata/oradb/system01.dbf
         2 /opt/oracle/db/oradata/oradb/sysaux01.dbf
         3 /opt/oracle/db/oradata/oradb/undotbs01.dbf
         4 /opt/oracle/db/oradata/oradb/users01.dbf
         5 /opt/oracle/db/oradata/oradb/tt.dbf
         6 /opt/oracle/db/oradata/tt02.dbf
         7 /opt/oracle/db/oradata/oradb/db01.dbf
         8 /opt/oracle/db/oradata/qiyi.dbf
8 rows selected.
3.查询表中记录对应的rowid
SQL> select rowid, id, name from dump;
ROWID                      ID NAME
------------------ ---------- --------------------------------------------------
AAATeFAABAAAU/pAAA       1000 tomsh
AAATeFAABAAAU/pAAB       1001 dumpceshi
SQL>
4.利用dbms_rowid包计算出记录对应的数据块
select dbms_rowid.rowid_block_number('AAATeFAABAAAU/pAAA') from dual;

SQL> select dbms_rowid.rowid_block_number('AAATeFAABAAAU/pAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAATEFAABAAAU/PAAA')
---------------------------------------------------
                                              85993
select dbms_rowid.ROWID_OBJECT('AAATeFAABAAAU/pAAA') from dual;

SQL> select dbms_rowid.ROWID_RELATIVE_FNO('AAATeFAABAAAU/pAAA') from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAATEFAABAAAU/PAAA')
---------------------------------------------------
                                                  1

5.dump数据文件号为1中的第85993数据块
SQL> alter system dump datafile 1 block 85993;
System altered.
6.跟踪文件内容如下：
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
Start dump data blocks tsn: 0 file#:1 minblk 85993 maxblk 85993
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4280297
BH (0x213f1e48) file#: 1 rdba: 0x00414fe9 (1/85993) class: 1 ba: 0x21268000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 79749 objn: 79749 tsn: 0 afn: 1 hint: f
  hash: [0x2d2d8e60,0x2d2d8e60] lru: [0x223f450c,0x217f209c]
  lru-flags: hot_buffer
  ckptq: [NULL] fileq: [NULL] objq: [0x213f67ec,0x2a9ff328]
  st: XCURRENT md: NULL tch: 0
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 0 rdba: 0x00414fe9 (1/85993)
scn: 0x0000.002b4c95 seq: 0x01 flg: 0x06 tail: 0x4c950601
frmt: 0x02 chkval: 0xe3f7 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7116A00 to 0xB7118A00
B7116A00 0000A206 00414FE9 002B4C95 06010000  [.....OA..L+.....]
B7116A10 0000E3F7 000E0001 00013785 002B4C56  [.........7..VL+.]
B7116A20 1FE80000 00031F02 00000000 00160001  [................]
B7116A30 00000892 00C00B43 000E0287 00002002  [....C........ ..]