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