oracle ORA-30926: 无法在源表中获得一组稳定的行

ORA-30926: unable to get a stable set of rows in the source tables

提问by Omnipresent

I am getting

我正进入（状态

ORA-30926: unable to get a stable set of rows in the source tables

ORA-30926: 无法在源表中获得一组稳定的行

in the following query:

在以下查询中：

  MERGE INTO table_1 a
      USING 
      (SELECT a.ROWID row_id, 'Y'
              FROM table_1 a ,table_2 b ,table_3 c
              WHERE a.mbr = c.mbr
              AND b.head = c.head
              AND b.type_of_action <> '6') src
              ON ( a.ROWID = src.row_id )
  WHEN MATCHED THEN UPDATE SET in_correct = 'Y';

I've ran table_1it has data and also I've ran the inside query (src) which also has data.

我已经运行table_1它有数据，并且我也运行了内部查询（src），它也有数据。

Why would this error come and how can it be resolved?

为什么会出现这个错误，如何解决？

回答by Pop

This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times.

这通常是由 USING 子句中指定的查询中的重复项引起的。这可能意味着 TABLE_A 是父表并且多次返回相同的 ROWID。

You could quickly solve the problem by using a DISTINCT in your query (in fact, if 'Y' is a constant value you don't even need to put it in the query).

您可以通过在查询中使用 DISTINCT 来快速解决问题（实际上，如果 'Y' 是一个常量值，您甚至不需要将其放入查询中）。

Assuming your query is correct (don't know your tables) you could do something like this:

假设您的查询是正确的（不知道您的表），您可以执行以下操作：

  MERGE INTO table_1 a
      USING 
      (SELECT distinct ta.ROWID row_id
              FROM table_1 a ,table_2 b ,table_3 c
              WHERE a.mbr = c.mbr
              AND b.head = c.head
              AND b.type_of_action <> '6') src
              ON ( a.ROWID = src.row_id )
  WHEN MATCHED THEN UPDATE SET in_correct = 'Y';

回答by DCookie

You're probably trying to to update the same row of the target table multiple times. I just encountered the very same problem in a merge statement I developed. Make sure your update does not touch the same record more than once in the execution of the merge.

您可能试图多次更新目标表的同一行。我刚刚在我开发的合并语句中遇到了同样的问题。确保您的更新在执行合并时不会多次触及同一记录。

回答by Tagar

How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)

如何解决 ORA-30926 错误？（文档 ID 471956.1）

1) Identify the failing statement

1) 识别失败的语句

alter session set events ‘30926 trace name errorstack level 3';

更改会话设置事件“30926 跟踪名称错误堆栈级别 3”；

or

或者

alter system set events ‘30926 trace name errorstack off';

更改系统设置事件“30926 跟踪名称错误堆栈关闭”；

and watch for .trc files in UDUMP when it occurs.

并在发生时注意 UDUMP 中的 .trc 文件。

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.

2) 找到 SQL 语句后，检查它是否正确（可能使用解释计划或 tkprof 来检查查询执行计划），如果最近没有这样做，则分析或计算相关表的统计信息。重建（或删除/重新创建）索引也可能有所帮助。

3.1) Is the SQL statement a MERGE? 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

3.1) SQL 语句是 MERGE 语句吗？评估 USING 子句返回的数据以确保连接中没有重复值。修改合并语句以包含确定性 where 子句

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.2) 这是通过视图的 UPDATE 语句吗？如果是这样，请尝试将视图结果填充到表中并尝试直接更新表。

3.3) Is there a trigger on the table? Try disabling it to see if it still fails.

3.3) 表上有触发器吗？尝试禁用它以查看它是否仍然失败。

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.4) 语句在“IN-Subquery”中是否包含不可合并的视图？如果查询具有“FOR UPDATE”子句，这可能会导致返回重复的行。参见错误 2681037

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

3.5) 该表是否有未使用的列？删除这些可以防止错误。

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 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.3) If the table is additionally Index Organized, see: Note 102932.1 - Monitoring Chained Rows on IOTs

4) 如果修改 SQL 不能解决错误，问题可能出在表上，尤其是当有链式行时。4.1) 对 SQL 中使用的所有表运行“ANALYZE TABLE VALIDATE STRUCTURE CASCADE”语句，以查看表或其索引中是否有任何损坏。4.2) 检查并消除表上的任何 CHAINED 或迁移行。有一些方法可以最大限度地减少这种情况，例如正确设置 PCTFREE。使用 Note 122020.1 - Row Chaining and Migration 4.3) 如果表是额外的 Index Organized，请参阅：Note 102932.1 - Monitoring Chained Rows on IOT

回答by Cee McSharpface

Had the error today on a 12c and none of the existing answers fit (no duplicates, no non-deterministic expressions in the WHERE clause). My case was related to that other possible cause of the error, according to Oracle's message text (emphasis below):

今天在 12c 上有错误，现有答案都不适合（没有重复，WHERE 子句中没有非确定性表达式）。根据 Oracle 的消息文本（重点如下），我的情况与该错误的其他可能原因有关：

ORA-30926: 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 activityor a non-deterministic where clause.

ORA-30926：无法在源表中获得一组稳定的行
原因：由于大型 dml 活动或非确定性 where 子句，无法获得一组稳定的行。

The merge was part of a larger batch, and was executed on a live database with many concurrent users. There was no need to change the statement. I just committed the transaction before the merge, then ran the merge separately, and committed again. So the solution was found in the suggested action of the message:

合并是更大批次的一部分，并且在具有许多并发用户的实时数据库上执行。没有必要更改声明。我只是在合并之前提交了事务，然后单独运行合并，然后再次提交。所以在消息的建议操作中找到了解决方案：

Action: Remove any non-deterministic where clauses and reissue the dml.

行动：删除任何非确定性 where 子句并重新发出dml。

回答by v8-E

SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 -  "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 occurred for me because of duplicate records(16K)

由于重复记录（16K），我发生了此错误

I tried with unique it worked.

我尝试了独特的它工作。

but again when I tried merge without unique same proble occurred Second time it was due to commit

但是当我再次尝试合并而没有出现唯一相同的问题时，第二次是由于提交

after merge if commit is not done same Error will be shown.

合并后，如果未完成提交，将显示相同的错误。

Without unique, Query will work if commit is given after each merge operation.

如果没有唯一性，如果在每次合并操作后都给出提交，则 Query 将起作用。

回答by Durban_legend

A further clarification to the use of DISTINCT to resolve error ORA-30926 in the general case:

在一般情况下使用 DISTINCT 解决错误 ORA-30926 的进一步说明：

You need to ensure that the set of data specified by the USING() clause has no duplicate values of the join columns, i.e. the columns in the ON() clause.

您需要确保 USING() 子句指定的数据集没有连接列的重复值，即ON() 子句中的列。

In OP's example where the USING clause only selects a key, it was sufficient to add DISTINCT to the USING clause. However, in the general case the USING clause may select a combination of key columns to match on and attribute columns to be used in the UPDATE ... SET clause. Therefore in the general case, adding DISTINCT to the USING clause will still allow different update rows for the same keys, in which case you will still get the ORA-30926 error.

在 OP 的 USING 子句仅选择一个键的示例中，将 DISTINCT 添加到 USING 子句就足够了。但是，在一般情况下，USING 子句可以选择要匹配的键列和要在 UPDATE ... SET 子句中使用的属性列的组合。因此，在一般情况下，将 DISTINCT 添加到 USING 子句仍将允许相同键的不同更新行，在这种情况下，您仍然会收到 ORA-30926 错误。

This is an elaboration of DCookie's answer and point 3.1 in Tagar's answer, which from my experience may not be immediately obvious.

这是对 DCookie 的回答和 Tagar 回答中的第 3.1 点的详细说明，根据我的经验，这可能不是很明显。