How to Troubleshoot ORA-30926 Errors – reference article for the new oracle dba





As a new oracle dba , sometimes you might see errors with merge statements  and the error is ORA-30926. The belkow metalink article gives you a complete overview on how to troubleshoot these kind of errors.

 

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


 

  Modified 25-JUL-2010     Type TROUBLESHOOTING     Status PUBLISHED  

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
  References


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

References

BUG:2681037 – ORA-30926 WHILE UPDATING USING CBO
BUG:4395296 – MERGE WITH APPEND HINT CORE DUMPS INSTEAD OF GIVING ORA-30926
BUG:7441667 – AFTER APPLYING PATCH 7275120, USER GETS ORA-30926. PATCH ROLLED BACK ALL IS FINE
NOTE:102932.1 – Monitoring Chained Rows on IOTs
NOTE:122020.1 – Row Chaining and Row Migration
NOTE:137202.1 – Oracle9i: Merge SQL statement
NOTE:96858.1 – ORA-30926: unable to get a stable set of rows in the source tables

 Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Errors


ORA-600[13012]; ORA-30926; 30926 ERROR

 

 Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.

 
Cancel    

 

 

Author: admin