Re: Extracting Data from a Corrupt Table using ROWID scans

Run an explain plan on all three versions of the query and see how Oracle solved each statement. In the third SQL statement the hint should have been ignored since the alias T referenced in the hint does not exist. The internal processing to find the first rowid may have allowed Oracle to skip over the bad row as the rdbms should have been able to jump to the block where AAAAeDAAkAAAJq6AAA would be if it existed and find the row without accessing the corrupted row though if this was true I would have expected the indexed access to also work.

It is possible if you are not the only person working on this that someone deleted the bad row or the corruption is in the a section of the block header that both indexed and full table scan access use but that the third access somehow skipped.

If the table has a PK or unique index then by selecting the key and rowid Oracle should get the information just from the index. Then you can use a loop to fetch each row by rowid capturing the 1578 error. This should allow you to salvage most all the data.

Or you can use the dbms_repair package on the table. See the Supplied Packages manual for information on using this package.

HTH -- Mark D Powell -- Received on Tue Jun 21 2005 - 08:57:33 CDT