Block Dump

Sometimes it is necessary to take block dump of a particular segment(table or index) to identify few things such as number of ITL slots allocated to support concurrency,etc.

A) Select the datafile where the segments are located.

SQL> select FILE_ID from dba_data_files where TABLESPACE_NAME=(select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

FILE_ID
----------
7
14
16

B) Get range of blocks corresponding to the segment.

select 'alter system dump datafile 7 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

select 'alter system dump datafile 14 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

select 'alter system dump datafile 16 block min '||c1||' block max '||c2||';' from (select block_id c1,(block_id + blocks -1) c2 from dba_extents where
segment_name='MY_TABLE_OR_INDEX' and owner='TABLE_INDEX_OWNER');

C) Do the block dump

The output of the previous step would be something similar to below. Just execute these statements and the output will be generated under user_dump_dest.

alter system dump datafile 7 block min 661 block max 664;
alter system dump datafile 7 block min 3633 block max 3632;
alter system dump datafile 7 block min 4181 block max 4180;