I have a corrupted block in the system tablespace. I used the standard query to identify the object that the block but it failed:
SQL> 1 SELECT segment_name 2 , segment_type 3 , owner 4 , tablespace_name 5 , block_id 6 , blocks 7 FROM sys.dba_extents 8 WHERE file_id = 1 9* AND 8002 BETWEEN block_id and block_id + blocks -1 FROM sys.dba_extents * ERROR at line 7: ORA-01578 (See ORA-01578.ora-code.com): ORACLE data block corrupted (file # 1, block # 8002) ORA-01110 (See ORA-01110.ora-code.com): data file 1: '/u100/oradata/fstst/system01.dbf'
Then I did this: ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
select owner , segment_name , segment_type , tablespace_name , block_id , blocks from dba_extents where file_id=1 and block_id in ( select max(block_id) from dba_extents where file_id=1 and block_id <= 8002 );
So, the object that the corrupt block belongs to is c_obj#.
Is that correct?
If so, and since c_obj# stores data about objects (metadata), it basically means that I can not access the object whose metadata is in that block. So, I'm trying to identify which object is lost. For example, if it's just an index, I should be able to export all the objects from database into a new database and rebuild the index, and resolve the corruption in that way. Any idea how I can identify the object whose metadata is lost?
I'm aware that Oracle support should be contacted, however a friend of mine asked me to look into this before they contact Oracle Support since they don't have a support contract anymore... BTW, this is 8.0.6, they identified this problem 3 months ago in their Peoplesoft application, and of course they don't have a good backup from which to recover...
