blocks|key|315008|text|您可能正在尝试多次更新目标表的同一行。我刚刚在我开发的merge语句中遇到了同样的问题。确保您的更新在执行合并时不会多次接触同一记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|315009|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3272402|text|这通常是由USING子句中指定的查询中的重复项引起的。这可能意味着TABLE_A是一个父表，并且多次返回相同的ROWID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3272403|您可以通过在查询中使用DISTINCT来快速解决这个问题(实际上，如果'Y‘是一个常量值，您甚至不需要将其放入查询中)。|3272404|假设您的查询是正确的(不知道您的表)，您可以这样做：|3272405|++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';|code-block|syntax|javascript|3272406|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

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).

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

<pre><code> 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 &lt;&gt; '6') src
 ON ( a.ROWID = src.row_id )
 WHEN MATCHED THEN UPDATE SET in_correct = 'Y';
</code></pre>

blocks|key|315986|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|315987|如何排除ORA-30926错误？(文档ID+471956.1)|blockquote|315988|315989|315990|1)识别失败的语句|315991|315992|+alter+session+set+events‘30926跟踪名称错误堆栈级别3’；|315993|315994|315995|或|315996|315997|+alter+system+set+events‘30926跟踪名称错误堆栈关闭’；|315998|315999|316000|并在发生这种情况时注意UDUMP中的.trc文件。|316001|2)找到SQL语句后，检查它是否正确(可能使用explain+plan或tkprof检查查询执行计划)，如果最近没有这样做，则分析或计算相关表的统计数据。重建(或删除/重新创建)索引也可能有所帮助。|316002|3.1)+SQL语句是MERGE吗？评估USING子句返回的数据，以确保联接中没有重复值。修改merge语句以包含确定性where子句|316003|3.2)这是通过视图的UPDATE语句吗？如果是这样，请尝试将视图结果填充到表中，并尝试直接更新表。|316004|3.3)桌子上有触发器吗？尝试禁用它，看看它是否仍然失败。|316005|3.4)语句中是否包含'+in+-Subquery‘中的不可合并视图？如果查询具有"FOR+UPDATE“子句，这可能会导致返回重复的行。请参阅Bug+2681037|316006|3.5)表中是否有未使用的列？删除这些可能会防止错误。|316007|4)如果修改SQL不能修复错误，那么问题可能出在表上，特别是在存在链接行的情况下。4.1)在SQL中使用的所有表上运行‘ANALYZE+TABLE+VALIDATE+STRUCTURE+CASCADE’语句，查看表或其索引中是否有任何损坏。4.2)检查并消除表上任何链接或迁移的行。有一些方法可以最小化这一点，例如正确设置PCTFREE。使用注释122020.1+-行链接和迁移4.3)如果表另外组织了索引，请参阅:注释102932.1+-监视IOT上的链接行|316008|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|1B|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|1C|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|1D|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|1E|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|1F|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|1G|7|@]|8|@]|9|$]]|$1|I|3|J|4|C|6|1H|7|@]|8|@]|9|$]]|$1|K|3|-4|4|5|6|1I|7|@]|8|@]|9|$]]|$1|L|3|-4|4|5|6|1J|7|@]|8|@]|9|$]]|$1|M|3|N|4|5|6|1K|7|@]|8|@]|9|$]]|$1|O|3|-4|4|5|6|1L|7|@]|8|@]|9|$]]|$1|P|3|Q|4|C|6|1M|7|@]|8|@]|9|$]]|$1|R|3|-4|4|5|6|1N|7|@]|8|@]|9|$]]|$1|S|3|-4|4|5|6|1O|7|@]|8|@]|9|$]]|$1|T|3|U|4|5|6|1P|7|@]|8|@]|9|$]]|$1|V|3|W|4|5|6|1Q|7|@]|8|@]|9|$]]|$1|X|3|Y|4|5|6|1R|7|@]|8|@]|9|$]]|$1|Z|3|10|4|5|6|1S|7|@]|8|@]|9|$]]|$1|11|3|12|4|5|6|1T|7|@]|8|@]|9|$]]|$1|13|3|14|4|5|6|1U|7|@]|8|@]|9|$]]|$1|15|3|16|4|5|6|1V|7|@]|8|@]|9|$]]|$1|17|3|18|4|5|6|1W|7|@]|8|@]|9|$]]|$1|19|3|-4|4|5|6|1X|7|@]|8|@]|9|$]]]|1A|$]]

<blockquote>
 How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)
</blockquote>

1) Identify the failing statement

<blockquote>
 alter session set events ‘30926 trace name errorstack level 3’; 
</blockquote>

or

<blockquote>
 alter system set events ‘30926 trace name errorstack off’; 
</blockquote>

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

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.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.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. 

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.5) 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 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

blocks|key|316013|text|今天在12c上出现了错误，并且没有一个现有的答案符合(+WHERE子句中没有重复的、不确定的表达式)。根据Oracle的消息文本(强调如下)，我的案例与该错误的其他可能原因有关：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|316014|316015|+ORA-30926:无法在源表中获取稳定的行集|blockquote|316016|316017|原因:由于大型dml活动或不确定的where子句，无法获取稳定的行集。|offset|length|style|BOLD|316018|316019|合并是一个更大的批处理的一部分，并在具有许多并发用户的实时数据库上执行。没有必要更改语句。我只是在合并之前提交了事务，然后单独运行合并，然后再次提交。因此，在消息的建议操作中找到了解决方案：|316020|316021|操作:删除任何不确定的where子句，并重新发出。|316022|316023|entityMap^0|0|0|0|0|Y|1|0|0|0|2N|0|0|0|P|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|X|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|Z|8|@$I|10|J|11|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|13|8|@$I|14|J|15|K|L]]|9|@]|A|$]]|$1|P|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|E|7|17|8|@$I|18|J|19|K|L]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|U|$]]

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):

<blockquote>
 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 activity or a non-deterministic where clause.
</blockquote>

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:

<blockquote>
 Action: Remove any non-deterministic where clauses and reissue the dml.
</blockquote>

blocks|key|316057|text|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.|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|316058|由于重复记录(16K)，此错误发生在me+上|unstyled|offset|length|style|BOLD|316059|我尝试了唯一的it+。|316060|但是，当我在没有唯一的情况下尝试合并时，第二次出现了同样的问题，这是由于提交|316061|合并后如果未完成提交，将显示相同的错误。|316062|如果不使用unique，则如果在每次合并操作后都给出commit，则查询将起作用。|316063|entityMap^0|0|L|1|0|0|B|0|0|12|0|0|K|0|0|15|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|V|8|@$G|W|H|X|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@$G|Z|H|10|I|J]]|9|@]|A|$]]|$1|M|3|N|5|F|7|11|8|@$G|12|H|13|I|J]]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@$G|15|H|16|I|J]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|17|8|@$G|18|H|19|I|J]]|9|@]|A|$]]|$1|S|3|-4|5|F|7|1A|8|@]|9|@]|A|$]]]|T|$]]

<pre><code>SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 - &quot;unable to get a stable set of rows in the source tables&quot;
*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.
</code></pre>
This Error occurred for me because of duplicate records(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.

blocks|key|315121|text|几个小时后，我还是没能解决这个问题。最后，我只对连接的两个表执行了select操作，创建了extract语句，并为表中的500行创建了单独的SQL+update语句。虽然很难看，但总比花几个小时尝试让查询生效要好得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|315122|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I was not able to resolve this after several hours. Eventually I just did a select with the two tables joined, created an extract and created individual SQL update statements for the 500 rows in the table. Ugly but beats spending hours trying to get a query to work.

I am getting 

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

in the following query:

<pre><code> 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 &lt;&gt; '6') src
 ON ( a.ROWID = src.row_id )
 WHEN MATCHED THEN UPDATE SET in_correct = 'Y';
</code></pre>

I've ran <code>table_1</code> it has data and also I've ran the inside query (<code>src</code>) which also has data. 

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

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

数据库

我得到了 ORA-30926:无法在源表中获取稳定的行集在以下查询中：  MERGE INTO table_1 a      USING       (SELECT a.ROWID row_id, 'Y'              FROM table_1 a ,table_2 b ,table_3 c        ...

问ORA-30926:无法在源表中获取稳定的行集
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-30926:无法在源表中获取稳定的行集EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-30926:无法在源表中获取稳定的行集
EN