How to dump Oracle Data Block?

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!!

Comments:

please put ,how to find dump block.

Posted by j.nagaraju on September 15, 2009 at 09:09 AM IST #

e

Posted by guest on March 18, 2013 at 11:12 PM IST #

show parameter USER_DUMP_DEST
to find the directory where it will ba saved

Posted by guest on March 31, 2014 at 02:25 PM IST #

i think the segment header_block should be read from dba_segments not dba_extents. block_id from dba_extents has different purpose

Posted by manish on May 07, 2014 at 05:55 AM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

What I learned about Oracle

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today