Oracle blog

Finding table extents after losing a datafile in LMT

What it means of losing a datafile in locally managed tablespace:

Finding the list of tables and the number of extents stored in a lost datafile could prove very important under some situations; you could either resort to old copies of those tables or make better decision about the impact of lost datafile.

This is not a problem in dictionary managed tablespace as all the extent related information is stored in dictionary objects, i.e., DBA_EXTENTS and DBA_SEGMENTS.

As all the extents map related information is stored in the actual datafiles, losing a datafile could very well mean losing extents information partially or fully.

If the datafile lost don't have any segment headers, then you can find all the segments and it's extent information from the following query:

select count(*), sum(blocks), sum(bytes) from dba_extents where file_id = &file_id and
segment_name in (select segment_name from dba_segments where extents is not null and header_file = &&file_id);

If the datafile lost have some segment headers stored in it, you can find the list of those segments by:

select segment_name, segment_type, owner from dba_segments where extents is null and header_file = &file_id);

And hence, it is highly advisable to backup dba_extents periodically.

posted by Saibabu Devabhaktuni @ 12:06 AM