Donald K. Burleson Oracle Tips

How to dump an Oracle segment header block

It is sometime very interesting to look inside Oracle data blocks and see the contents on the data block. 

Peering inside an Oracle data block is especially useful then you are experiencing buffer busy waits.  Buffer busy wait contention is caused by a shortage of some resource or a latch being held on a data block.  By locating the contents of the data block, you can see the contents of the data block and segment header so you can adjust the storage parameters that influence the contention.  This is most commonly the freelist or freelist_groups parameter, and can also be a too-high PCTUSED setting for a table.

The following script will allow you to do this.

set heading off;

spool dump_em.sql;

select

   'alter session set events ''immediate trace name blockdump level '||

   to_char((header_file*16777216)+header_block)||''';'

from

   dba_segments

where

   segment_name = 'CUSTOMER_TABLE';

spool off;

cat dump_em.sql

@dump_em

While Oracle does not provide the DSECTS for the table internals, this script will dump and partially format the data block.  It is very interesting to view the internal linkages within an Oracle table.  Remember, nothing will impress your boss more than seeing you reading a cryptic dump of an internals block, so you may want to keep a listing on your desk at all times, just in case a manager drops by.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm