Oracle uses dump to dump blocks

When doing some kernel comparison research on Oracle, you will use the dump command to dump the information in the database to the trace file. This article briefly describes the use of the dump command to dump information from a data block to a trace file.

Experimental environment: rhel7.2+11.2.0.4

Grammar:

alter system dump datafile {File no} block {Block no};

alter system dump datafile 4 block 129;

alter system dump datafile {File no} block min {Block min} block max {Block max};

alter system dump datafile 4 block min 129 block max 133;

alter system dump datafile '{name}' block {Block no};

alter system dump datafile '{name}' block min {Block min} block max {Block max};

1. Create a test table

zx@ORA11G>drop table t;Table dropped.zx@ORA11G>create table t as select * from dual;Table created.zx@ORA11G>select * from t;D-XXX3 rows selected.

2. Query the data block number of the data table

zx@ORA11G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------ ------------------------------------ 4 37771 4 37771 4 377713 rows selected.

From the query above, you can see that all three pieces of data are stored in block 37771 of file number 4.

3. Use dump command to dump block information into trace file and find corresponding trace file.

zx@ORA11G>alter system dump datafile 4 block 37771;System altered.zx@ORA11G>select value from v$diag_info where name='Default Trace File';VALUE-------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5332.trc

4. View the contents of the trace file

The above intercepts some information: object_id containing the table, transaction slot information on the table, details of the three rows of data in the table, and so on.

The dumped trace file contains a lot of information. If you can understand and analyze such trace file, it means that you are one step closer to the master.

Different versions of the database dump out of the trace file, the source is different, may be from disk or buffer cache, interested students can test themselves.

Oracle Core Essential Internals for DBAs and Developers