Just a quick summary on how I resolved this problem (see below for the problem definition). I used a stored proc to dynamically execute a "select /*+ full(t) noparallel(t) */" against all appropriate objects to identify the objects that were affected. The result was 1 synonym, 2 views and 3 tables were affected. Fortunately the 3 tables contained old audit data and were not needed anymore, and the views and synonym could easily be recreated. So, I set event = "10231 trace name context forever, level 10" to export all data, except the corrupted block. However, export failed with an ora-600 on the export, basically saying that it's trying to export a table for which it can't find it's location. After further research, I added "and o$.name not in ('PS_AUDIT_0000222', 'PS_AUDIT_0000223', 'PS_AUDIT_0000224' )" to the export views sys.EXU8CLU and SYS.EXU8TAB, thus telling export not to even try to deal with the 3 affected tables. It worked and the rest is the standard exp/imp story... Thank you for all of your help. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Vlado Barun Sent: Thursday, January 06, 2005 6:24 PM To: oracle-l@xxxxxxxxxxxxx Subject: Identifying the content of a corrupted block in system tablespace 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: ORACLE data block corrupted (file # 1, block # 8002) ORA-01110: 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 ); OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BLOCKS ----- ------------ ------------ ---------------- -------- ------ SYS C_OBJ# CLUSTER SYSTEM 7978 25 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... Vlado Barun, M.Sc. Mobile: 865 335 7652 AIM: vbarun2 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l