Solution for long running query against DBA_EXTENTS to find the corrupted segment

Problem

You are running a query against DBA_EXTENTS to find the segment information (most probably you would be using
this to find a CORRUPTED segment/object) for a particular FILE_ID and BLOCK_ID.

Query might look something like this.

select owner,segment_name,segment_type
from dba_extents
where file_id =
and between block_id and (block_id+(blocks-1));

This query might run longer and sometimes you may lose your patience waiting for it.

Solution

• Dump the block and take the object id using the below command

alter system dump datafile block ;
Eg.alter system dump datafile 1811 block 20000;

FYI, this command generate a trace file in the user_dump_dest directory

• Grep the object number from the trace file.

Locate the trace file and grep the object id which will be presented in HEX value.

grep "seg/obj" op01csdb01_ora_13143.trc

seg/obj: 0xe024a2 csc: 0x878.a5583a53 itc: 50 flg: E typ: 1 - DATA

• Find the object information from DBA_OBJECTS

select owner,object_id,data_object_id,object_name,subobject_name

from dba_objects

where (object_id=to_number('E024A2', 'xxxxxx')

or

data_object_id=to_number('E024A2', 'xxxxxx')

);

Note : the count of 'x' should be equal to the number of characters in the source.

Eg. E024A2 - is 6 character string, hence i used 'xxxxxx'

Keywords : query; dba_extents; long; slow; corrupt ; segment; object