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
/



| ©2010 by Barry Chase |
Credits: