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

Last updated on FEBRUARY 04, 2022

Applies to:

Goal

 

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.