Re: Urgent Help!!! ORACLE data block corrupted

From: Proud Immigrant <immigrant_at_a.new.country> >> Does this mean the data in this segment is lost forever? <<

Write a query that selects the segment_name from sys.dba_extents where the  corrupted block is between the starting block of an extent and its ending  block (starting block + length in blocks).

If the corrupted object is an index, just drop and re-create it. If the  corrupted object is a user table then what you do depends on several factors.  If the table is static, drop it and recreate it from your last export. If the  table is dynamic and the data is important then you will need to use pl/sql to  try to savage it.

The basic process is that you look for the prime index and write a query to  create a driving table selecting only the indexed columns in index order.  Oracle should answer the query using only the index. Now write the pl/sql  routine with a cursor loop that for each row in the driving table fetches the  cooresponding row from the original table, insert the readable row into a new  table. Provide an exception block to capture the corruption error condition  so that you proceed to process the next driver row. This way your new version  will end up holding all rows that are not corrupted or inaccessable due to  block header corruption.

The are different kinds of corruption. Row data could be corrupted meaning all  but one or a few rows are accessible by rowid, i.e., index, or the block  header can be corrupted preventing Oracle from accessing rows within a block.  I used the above patten to salvage all but eight rows in a 4.3 million row  table.

If the corrupted object is part of system (owner by sys, not just stored in  system tablespace), you should seek advise from Oracle support! You can use the analyze table command validate structure cascade to  create a trace file that Oracle support can review. They may be able to  provide rowid information or an inti.ora event that proves usful.

Good Luck!
Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Tue Oct 14 1997 - 00:00:00 CDT