ORA-30926

How to Troubleshoot ORA-30926 Errors? [ID 471956.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.5 - Release: 8.1.7 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***

Purpose

ORA-30926 can occur when a SQL statement is executed, and the reason may not always be apparent.
This note is to help you identify the cause.
The official description of this error is [Note 96858.1]:

ORA-30926 (formerly ORA-600 [13012])
Error: ORA-30926
Text: Unable to get a stable set of rows in the source tables.
--------------------------------------------------------------------
Cause: A stable set of rows could not be got because of large DML activity or a non-deterministic where clause.
Action: Remove any non-deterministic where clauses and reissue the DML.

(This error occurs with the Cost based Optimizer but not with RULE.)

Last Review Date

December 21, 2007

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

1. Identify the failing statement. If this is not known, set an event to produce a .trc file in the UDUMP directory. This will show the failing SQL on the first page.

- If the error occurs in your SQLPLUS session, use:
SQL> alter session set events ‘30926 trace name errorstack level 3’;
Run the failing script/procedure etc.
This event can be disabled by ending the session or by using:
SQL> alter session set events ‘30926 trace name errorstack off’;

- If the error does not occur in a SQLPLUS session, set the event at system level and watch for .trc files in UDUMP when it occurs.
To set it:
SQL> alter system set events ‘30926 trace name errorstack level 3’;
To unset it after trace file is produced:
SQL> alter system set events ‘30926 trace name errorstack off’;

(If you have any difficulty reviewing the .trc files, log a Service Request with Oracle Support and upload the .trc files.)

2. Having found the SQL statement, check if it is correct (perhaps using explain plan or tkprof to check the query execution plan) and analyze or compute statistics on the tables concerned if this has not recently been done. Rebuilding (or dropping/recreating) indexes may help too.

3. This error can have a variety of possible causes. Some of these which have been seen are listed below:

3.1. Is the SQL statement a MERGE?
MERGE is a deterministic statement. You therefore need to evaluate the data returned by the USING clause to ensure that there are no duplicate values in the join. Modify the merge statement to include a deterministic where clause.
Additionally, check out this note:
Note 137202.1 - Oracle9i: Merge SQL statement

3.2. Is this an UPDATE statement via a view?
If so, try populating the view result into a table and try updating the table directly.

3.3  Is there a trigger on the table? Try disabling it to see if it still fails. If it does, see BUG:7441667, which applies to 9.2.0.8 on Windows.

3.4  Does the statement contain a non-mergeable view in an 'IN-Subquery'? This can result in duplicate rows being returned if the query has a "FOR UPDATE" clause.
See Bug 2681037

3.55. Does the table have unused columns? Dropping these may prevent the error.

4.  If modifying the SQL does not cure the error, the issue may be with the table, especially if there are chained rows.

4.1. Run the ‘ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE’ statement on all tables used in the SQL to see if there are any corruptions in the table or its indexes.

4.2. Check for, and eliminate, any CHAINED or migrated ROWS on the table. There are ways to minimize this, such as the correct setting of PCTFREE.
Use Note 122020.1 - Row Chaining and Migration

4.33. If the table is additionally Index Organized, see:
Note 102932.1 - Monitoring Chained Rows on IOTs