How To identify a 'corrupt' row when error is raised but no row information provided (Doc ID 869305.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.1.0.7 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.

Goal

Checked for relevance on 31-Oct-2011

In a number of cases an error is hit indicating a corrupt row/column but no information is shown to indicate which row/column is causing the problem in question.

The purpose of this note is to identify the ROWID for which an error was raised, as well as to create a salvage table containing all rows which can be read successfully.

Depending on the error being signaled this note might be not be applicable (as not all errors can be caught / skipped in PLSQL). The following errors have been tested using the script below:
- ORA-1410 - "invalid ROWID"
- ORA-12899 - value too large for column "<table>.<column>" (actual: XX, maximum: YY)
- ....
==> Behaviour for other errors depends on whether the error can be trapped in an exception handler and on whether the PL/SQL block continues/succeeds

(in case the ROWID cannot be read from the table there is an requirement to have an index which can be used, as with all 'corruption' related issues never drop indexes until the moment in time that all salvage/diagnostic  actions are completed)

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms