RE: find file_id or block_id from data block address

Prem

>We don't have the packages you said on our PROD database.

The package exists in 7.3 as well... why not installing it?

>SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER FROM SYS.DBA_EXTENTS WHERE
>537037017 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
>
>This did not give me any result. My doubt is whether we can put the data block
>address ( 537037017) in the above query !!! Is that right ?

You cannot use a DBA in this way. In fact the DBA contains the file number AND the block \ number.

>I also tried the following in another database which has got the
>packages you said :
>(is it okay to execute the below on any database which has got those packages
>or is it to be done on the problem database only)
>
>sys.dbms_utility.data_block_address_block(537037017) = 166105
>sys.dbms_utility.data_block_address_file(537037017) = 128

IMO in 7.3 the file number part of the DBA is 8 bits (from 8.0 it is 10 bits). Therefore \ the correct file number is 32 (not 128).

>Is there any other way i can find out the file_id / object# from the
>above ORA-600 error.
>The trace file too did not give me any pointer about the file# or object# .

DBMS_UTILITY (or manual decoding) is the way to go. Notice that if you have a corruption \ problem the DBA could be completely wrong and therefore point to a non existent block...

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l