Extracting Data from a Corrupt Table -Phase 2

In phase 1, I have demonstrated how I can extract data from table around corrupt blocks. In this post I will try to explain how I can extract data from corrupt block itself.

If the corrupt object is a TABLE or CLUSTER or LOBSEGMENT then it must be understood that the data within the corrupt block is lost. Some of the data may be salvageable from a HEX dump of the block, or from columns covered by indexes. As the corrupt block itself is “corrupt” then any data extracted from the block should be treated as suspect. The main methods of getting the rows from the corrupt block itself are:

- For TABLE blocks Oracle Support can use a tool which attempts to interpret the block contents.

- Use any existing indexes on the table to extract data for columns covered by the index where the ROWID falls inside the corrupt block.

- It may be possible to use LogMiner on the redo stream to find the original inserts/updates which loaded the data to the problem block. The main factor here is WHEN the data was actually put in the block. eg; row 2 may have been inserted yesterday but row 1 may have been inserted 5 years ago.

If there are any indexes on the corrupt table then it is possible to get some information about what data was in the corrupt block from the index. This requires selecting indexed columns from the table for rowids in the corrupt block. We already know the ROWID range covered by the corrupt block from the SELECT dbms_rowid.rowid_create … statements ( from previous phase 1).

To extract the column data use one of the following forms of select statement:

If the columns required at NOT NULLable you can use a fast full scan:

SELECT /*+ INDEX_FFS(X ) */ , … FROM X WHERE rowid >= ‘’ AND rowid< ‘’ ;

If the columns required are NULLable then you cannot use an index fast full scan and must use a range scan. This requires you to know a minimum possible value for the leading index column to ensure you enable the index scan:

SELECT /*+ INDEX(X ) */ , … FROM X WHERE rowid >= ‘’ AND rowid < ‘’ AND >= ;

Using this technique for all indexes on the table may be able to retrieve some of the data. See for which columns make up each index.