How to get the object when you have a file# and a block#

Often you get a message (for instance, corruption) or query dynamic table (for instance, hot block) giving you a file and a block. This tip will show you how to retrieve the object belonging to these informations. If you get the file id (say &file_id) and the block id (say &block_id), your retrieve the object with:

Set linesize 120 
Set heading off 
Set feedback off 

Select 'Block &block_id of file '||f.file_name||' : 
   '||decode(e.owner, NULL, e.segment_name, 
                      e.owner||'.'||e.segment_name)|| 
         decode(e.partition_name, NULL, '', '.'||e.partition_name)|| 
         ' ('||e.segment_type||')' obj 
from dba_extents e, dba_data_files f 
where f.file_id = &file_id 
  and e.file_id = &file_id 
  and &block_id between e.block_id and e.block_id+e.blocks-1 
/ 
If you have the file name (say &file_name) and the block id:
Select 'Block &block_id of file &file_name : 
   '||decode(e.owner, NULL, e.segment_name, 
                      e.owner||'.'||e.segment_name)|| 
         decode(e.partition_name, NULL, '', '.'||e.partition_name)|| 
         ' ('||e.segment_type||')' obj 
from dba_extents e, dba_data_files f 
where f.file_name = '&file_name' 
  and e.file_id = f.file_id 
  and &block_id between e.block_id and e.block_id+e.blocks-1 
/