jump to navigation

How to dump Oracle Data Block? September 13, 2009

Posted by sendtoshailesh in Tuning.
Tags: ,
trackback

Often while doing instance tuning or sql tuning, Internal structure of a Oracle Data block is important to know. Moreover when system does not show a significant direction to do troubleshooting. In this blog, I am explaining to how to extract dump of a oracle data block.

dump shows following details which may be significant to find the rationale of problem of state of block:

  • contents of the block for the given datafile number and the block number
  • how the data is stored internally
  • list the contents of rows(Table Block) or index keys( Index Block)
  • extent map in segment header block
  • free extent pool in the undo segments header blocks
  • the SCN of the block (useful in complex recovery scenario)

To dump single block use following command:
alter system dump datafile block ;

To dump multiple block use following command:
alter system dump datafile block min block max ;

To dump the segment header block and the data block of a given segment:

select file_id, block_id, blocks
from   dba_extents
where  segment_name = ‘TEST’;

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1      29081          8

To dump the segment header block
alter system dump datafile 1 block 29081;

To dump the data block next to the segment header
alter system dump datafile 1 block 29082

To dump both the blocks at the same time
alter system dump datafile 1 block min 29081 block max 29082;

Wait and watch, I will update this blog to have more example on how to investigate the dump of block.

With above method, Oracle will dump all info in a file in USER_DUMP_DIRECTORY, one can find the file with instance name and OSPID.

Enjoy!!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: