How To identify a 'corrupt' row when error is raised but no row information provided
Last updated on NOVEMBER 28, 2016
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.
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)
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