1.建立測試表SQLgt; create table dump (id number,name varchar(20));Table created.SQLgt; insert into dump values (1000,

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.查詢表在數(shù)據(jù)文件以及塊號
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.查詢表中記錄對應(yīng)的rowid
SQL> select rowid, id, name from dump;
ROWID????????????????????? ID NAME
------------------ ---------- --------------------------------------------------
AAATeFAABAAAU/pAAA?????? 1000 tomsh
AAATeFAABAAAU/pAAB?????? 1001 dumpceshi
SQL>
4.利用dbms_rowid包計算出記錄對應(yīng)的數(shù)據(jù)塊
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數(shù)據(jù)文件號為1中的第85993數(shù)據(jù)塊
SQL> alter system dump datafile 1 block 85993;
System altered.
6.跟蹤文件內(nèi)容如下：
*** 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........ ..]