How to Dump a Block in Oracle

<<Back to DB Administration Main Page

Dumping Oracle Database Blocks

As we already know the data in oracle database resides in Tablespace (Logical Container) and each Tablespace has one or more datafile (Physical storage) so lets start with this
Step1> Create a tablespace
SQL> create tablespace TEST_AU_OS datafile '/u01/dbatest1/stage/database/db01.dbf' size 100M;
Tablespace created.
Step2> Find the Detail of your Tablespace and Datafile
SQL> select f.FILE#, f.NAME "File", t.NAME "Tablespace" from V$DATAFILE f, V$TABLESPACE t where t.NAME='TEST_AU_OS' and f.TS# = t.TS#;
     FILE# File                                                         Tablespace
---------- ------------------------------------------------------------ ---------------
        27 /u01/dbatest1/stage/database/db01.dbf                        TEST_AU_OS
Step3> Lets Create A Table and Populate Some Data
SQL> create table test (n number, name varchar2(16)) tablespace test_au_os;
Table created.
SQL> insert into test values (1,'TEST_FOR_FUN');
1 row created.
SQL> commit;
Commit complete.

At this point we know that my data is stored in a table called "TEST" which is stored in a Tablespace called "TEST_AU_OS" and data physically in a datafile  /u01/dbatest1/stage/database/db01.dbf which belongs to "TEST_AU_OS" tablespace
Datafile is constructed with database blocks or in other words the space to a datafile is allocated in terms of data blocks( standard DB Block Size is 8K). So let us find out the DB Block Size and Actual Block number which hold my piece of data
Step4> Find DB Block Size and Block Number for Particular Piece of Data
SQL> select ROWID,n,name from test where n=1;
ROWID                       N NAME
------------------ ---------- ----------------
AAAR4cAAbAAAACGAAA          1 TEST_FOR_FUN
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAR4cAAbAAAACGAAA') "Block number" from DUAL;
Block number
------------
         134
SQL> select BLOCK_SIZE from V$DATAFILE where FILE#=27;
BLOCK_SIZE
----------
      8192 <= Bytes
At this point I know the location of my Data which is stored in Block number 134 and BlockSize of each block is 8K.
Once you have the block number you can dump it using alter system dump datafile command.
Step5> Dump the Individual Block
SQL> alter session set tracefile_identifier ='Block_dump';
Session altered.
SQL> ALTER SYSTEM DUMP DATAFILE 27 BLOCK 134;
System altered.

You can inspect the trace file created for block dump to find the data stored in plain text format
$cat TESTA_ora_16058_Block_dump.trc |grep TEST_FOR_FUN

Note:- You can dump individual block or multiple block at a time.
to dump multiple block at once use command below
ALTER SYSTEM DUMP DATAFILE absolute_file_number  BLOCK MIN minimum_block_number BLOCK MAX maximum_block_number;
absolute_file_number <= DataFile Number
minimum_block_number  <= Starting block Number 
maximum_block_number;<= Ending Block number

Reading Data Stored in Database Using strings Command

Since my datafile is located in a filesystem mount point, if I know the disk and got the root access I can even read the data using strings command on this disk
Let us see how
$df |grep /u01/dbatest1

Login as root user to read the data from /dev/xvde disk
#strings /dev/xvde |grep TEST_FOR_FUN

Reading and Dumping Data Stored in Database Using dd Command

dd is another very interesting utility which we can use to read or dump the data directly from the disk.

If I know my datafile name (see step2) together with Block Number and Block Size (see Step 4) where my data is stored I can directly read this blog from disk itself using dd utility even if the database is down. Let us see how

$dd if=/u01/dbatest1/stage/database/db01.dbf bs=8192 skip=142 count=1|strings

Where

bs<= Database Block Size

Skip<= BlockNumber+Header Blocks(ie 134+8)

Count<= Number of Block to be red or dumped 

How to Dump the Header Block

Procedure of dumping the header block is same as dumping the normal block. Find the header block

using below query

 select header_file, header_block from dba_segments where segment_name = ='TABLE_NAME';

SQL>  select header_file, header_block from dba_segments where segment_name = 'TEST';

HEADER_FILE HEADER_BLOCK
----------- ------------
         27          130