Oracle ORA-600 [12700] "Index entry Points to Missing ROWID"

Oracle ORA-600 [12700] "Index entry Points to Missing ROWID"

ERROR:

Format: ORA-600 [12700] [a] [b] [c] [d] [e]

VERSIONS:

versions 6.0 to 9.2

DESCRIPTION:

Oracle is trying to access a row using its ROWID, which has been

obtained from an index.

A mismatch was found between the index rowid and the data block it is

pointing to. The rowid points to a non-existent row in the data block.

The corruption can be in data and/or index blocks.

ORA-600 [12700] can also be reported due to a consistent read (CR)

problem.

The information dumped to the trace file varies greatly between releases:

ARGUMENTS:

There are two formats of this error.

For Oracle 7.3 and earlier, there are two additional arguments:

Arg [a] Data Block Address (DBA)

Arg [b] Row Slot Number (Provides the location of the row in the data block)

For Oracle 8i and 9i, there are up to five additional arguments:

Arg [a] Data Object ID

Arg [b] Table Relative Data Block Address (RDBA)

Arg [c] Row Slot Number of the row in the data block

Arg [d] If present - Index Relative Data Block Address (RDBA)

Arg [e] If present - Index Row Source Class

@ Note that Args [d] and [e] were added to the OERI arguments via an enhancement (see bug 2294812)

@ Index Row Source Class definitions are defined in rwsdef.h

The arguments of the ORA-600 [12700] contain information obtained

from the index we are using.

FUNCTIONALITY:

USER/ORACLE INTERFACE LAYER

IMPACT:

POSSIBLE CORRUPTION

Diagnosis:

This can be due to a real corruption OR due to a consistent read

problem. The exact information dumped to the trace file varies

greatly between releases.

- ANALYZE TABLE VALIDATE STRUCTURE CASCADE to show if it is a

real corruption or not, or perform a full range scan via the index.

If this shows as a problem, treat the issue as a corruption. This

requires dropping and recreating the index. It would be wise to dump

the affected blocks (data and index) before recreating the index. It

may also be useful to dump the redo for the data and index block for

root cause analysis.

- If the analyze succeeds, this is probably a consistent read problem.

The most common cause of the problem is a long(ish) running query

which scans through an index on which DML (update/insert) is

currently occurring and causing 'index-splitting' to occur.

In this case the longer running the query the more chance of it

hitting one of these blocks thus forcing a CR (consistent read)

back to a pre-split time. In this case, the CR block can have

an index entry marked as valid (incorrectly) whereas the data-block

has the row marked deleted (correctly).

Workarounds:

- Try not to use the index.

- For CR issues, force a dummy sort to pre-allocate the row source up

front. I.e., process the rows in a presorted order rather than unsorted.

By doing so, we reduce possible 'index-splitting'.