Just a quick summary on how I resolved this problem (see below for the
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.
On Behalf Of Vlado Barun
Sent: Thursday, January 06, 2005 6:24 PM
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
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';
and block_id in (
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