{"id":1840,"date":"2025-04-21T22:06:23","date_gmt":"2025-04-21T20:06:23","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1840"},"modified":"2025-04-21T22:06:25","modified_gmt":"2025-04-21T20:06:25","slug":"ora-30926-in-oracle-23ai-whats-changed","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/ora-30926-in-oracle-23ai-whats-changed\/","title":{"rendered":"ORA-30926 in Oracle 23ai: What\u2019s Changed?"},"content":{"rendered":"\n<p>One of the topics I&#8217;ve found very interesting as I started blogging was the sometimes strange behavior of the <strong>MERGE<\/strong> statement, the reasons for <strong>ORA-30926<\/strong> and what it has to do with write consistency. My first blog post about it was almost exactly ten years ago. Time for a follow up! Especially because there are some changes in Oracle 23ai.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>If you don&#8217;t have time to read both posts from the past (the <a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926\/\" target=\"_blank\" rel=\"noreferrer noopener\">first<\/a> and the <a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926-part-2-or-differences-in-write-consistency-between-update-and-merge\/\" target=\"_blank\" rel=\"noreferrer noopener\">second<\/a>) here is the brief summary:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>ORA-30926<\/strong> is thrown if the source data for the <strong>MERGE<\/strong> is not unique with respect to the ON-condition, thus attempting to update the same row more than once<\/li>\n\n\n\n<li>At the same time Oracle treats the condition to throw <strong>ORA-30926<\/strong> as the reason to attempt a DML statement restart &#8211; this behavior seemed to be a bug. This restart caused by the duplicates will be, of course, unsuccessful because those duplicates are still there during the restart; but it means a significantly increased amount of work needs to be done before the <strong>ORA-30926<\/strong> error is thrown anyway.<\/li>\n\n\n\n<li>The behavior of the <strong>MERGE<\/strong> statement in context of write consistency and DML restarts is different from that of the <strong>UPDATE<\/strong>. Merge is not tracking columns in the ON-clause, but instead it is tracking columns in the SET-clause<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">New Error Messages<\/h2>\n\n\n\n<p>One of the many enhancements in Oracle 23ai are the improved error messages. In July 2023 <a href=\"https:\/\/blogs.oracle.com\/database\/post\/error-help-portal\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">Gerald Venzl announced<\/a> the <a href=\"https:\/\/docs.oracle.com\/error-help\/db\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">new error help portal<\/a>.  Many of the error messages that used to be a bit cryptic are now easier to understand and also provide additional information.  It was long overdue and good job, Oracle! <\/p>\n\n\n\n<p>How about <strong>ORA-30926?<\/strong> Do we also have a better error message? Yes!<\/p>\n\n\n\n<p>Just compare this one, which we had since Oracle 9i (Figure 1) with the new error message (Figure 2):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"452\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c.jpg\" alt=\"\" class=\"wp-image-2469\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c.jpg 800w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c-300x170.jpg 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c-768x434.jpg 768w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_21c-624x353.jpg 624w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/a><\/figure>\n\n\n\n<p><strong><em>Figure 1: ORA-30926 prior to 23ai<\/em><\/strong><\/p>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"472\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23.jpg\" alt=\"\" class=\"wp-image-2470\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23.jpg 800w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23-300x177.jpg 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23-768x453.jpg 768w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/30926_23-624x368.jpg 624w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/a><\/figure>\n\n\n\n<p><strong><em>Figure 2: ORA-30926 from 23ai onwards<\/em><\/strong><\/p>\n\n\n\n<p>When reading the old message, it is noticeable that the duplicates in the source leading to multiple updates of the same row in the target were not mentioned at all. It seems that in the past Oracle tried to cover two different causes for an error situation with just one action and one error message. Now it has been separated. We have a message for <strong>ORA-30926<\/strong> clearly explaining the reason for updating the same row twice (even providing the ROWID) as having duplicates in the source. And we&#8217;ve got a new error in 23ai &#8211; <strong>ORA-14359<\/strong> (Figure 3).<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"457\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23.jpg\" alt=\"\" class=\"wp-image-2474\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23.jpg 800w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23-300x171.jpg 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23-768x439.jpg 768w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2025\/04\/14359_23-624x356.jpg 624w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/a><\/figure>\n\n\n\n<p><strong><em>Figure 3: ORA-14359 in Oracle 23ai<\/em><\/strong><\/p>\n\n\n\n<p>The description sounds like the one we had for <strong>ORA-30926<\/strong> in the past, doesn&#8217;t it? But it better explains that not just some hypothetical &#8220;large DML activity&#8221; is relevant, but specifically concurrent DML modifying the WHERE clause columns. This could lead to a DML restart and if it is unsuccessful, we&#8217;ll get <strong>ORA-14359<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Changed Behavior<\/h2>\n\n\n\n<p>The new, more comprehensible and helpful error messages are one (good) thing, but to what extent has behavior also changed? Can we still see more work done than needed, when we get <strong>ORA-30926<\/strong>? Let&#8217;s check.<\/p>\n\n\n\n<p>For my test I will use a slightly modified fact table SALES from Oracle&#8217;s sample schema Sales History (SH). There is no reason for my modification here, I was just using it for another test and then just reused it here. Here is the setup (Listing 1):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE SEQUENCE IF NOT EXISTS SEQ_SALES;\n\nCREATE TABLE IF NOT EXISTS SALES_TGT (\n  SALE_ID        NUMBER        PRIMARY KEY,\t\n  PROD_ID        NUMBER        NOT NULL,\n  CUST_ID        NUMBER        NOT NULL,\n  TIME_ID        DATE          NOT NULL,\n  AMOUNT_SOLD    NUMBER(10,2)  NOT NULL  );\n\nINSERT INTO SALES_TGT  \nSELECT seq_sales.nextval as sale_id, prod_id, cust_id, time_id, amount_sold \nFROM SH.SALES;\n\n-- 5 percent we want to use for update\nCREATE TABLE IF NOT EXISTS sales_src AS \nSELECT sale_id, prod_id, cust_id, time_id\n,      ROUND(amount_sold*1.02,2) AS amount_sold\nFROM sales_tgt SAMPLE (5);\n\n-- add five duplicates\nINSERT INTO sales_src (sale_id, prod_id, cust_id, time_id, amount_sold)\nSELECT sale_id, prod_id, cust_id, time_id, round(amount_sold*1.01,2) amount_sold\nFROM   sales_src \nFETCH FIRST 5 ROWS ONLY;\n\nCOMMIT;\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 1: Setup<\/em><\/strong><\/p>\n\n\n\n<p>As a first step, I will run a MERGE in 19c (19.26). We can see that each step in the execution plan has been started three times (column Starts). You can read more about that, why and how it happens in <a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926-part-2-or-differences-in-write-consistency-between-update-and-merge\/\" target=\"_blank\" rel=\"noreferrer noopener\">my old blog post.<\/a><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n&gt;&gt; MERGE \/*+ gather_plan_statistics *\/ INTO sales_tgt s\nUSING sales_src a\nON ( a.sale_id = s.sale_id  )\nWHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold\nWHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id, \n                              time_id, amount_sold)\n        VALUES (seq_sales.nextval, a.prod_id, a.cust_id, \n                a.time_id, a.amount_sold)\nError at line 1\nORA-30926: unable to get a stable set of rows in the source tables\n\n----------------------------------------------------------------------\n| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |\n----------------------------------------------------------------------\n|   0 | MERGE STATEMENT       |           |      3 |        |      0 |\n|   1 |  MERGE                | SALES_TGT |      3 |        |      0 |\n|   2 |   VIEW                |           |      3 |        |  50131 |\n|   3 |    SEQUENCE           | SEQ_SALES |      3 |        |  50131 |\n|*  4 |     HASH JOIN OUTER   |           |      3 |  50127 |  50131 |\n|   5 |      TABLE ACCESS FULL| SALES_SRC |      3 |  50127 |    150K|\n|   6 |      TABLE ACCESS FULL| SALES_TGT |      3 |    918K|    918K|\n----------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 2: DML restart in 19c<\/em><\/strong><\/p>\n\n\n\n<p>But running the same MERGE in Oracle 23ai (23.7 Free), we not only get a better error message but can also see that the statement stops after processing 26 rows (column A-Rows) from the target table and there are no attempts to restart (Starts=1)<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n&gt;&gt; MERGE \/*+ gather_plan_statistics *\/ INTO sales_tgt s\nUSING sales_src a\nON ( a.sale_id =  s.sale_id  )\nWHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold\nWHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id, \n                              time_id, amount_sold)\n        VALUES (seq_sales.nextval, a.prod_id, a.cust_id, \n                a.time_id, a.amount_sold)\nError at line 3\nORA-30926: The operation attempted to update the same row (rowid: &#039;AAARkpAAYAAAIXjAAZ&#039;) twice.\n\n----------------------------------------------------------------------\n| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |\n----------------------------------------------------------------------\n|   0 | MERGE STATEMENT       |           |      1 |        |      0 |\n|   1 |  MERGE                | SALES_TGT |      1 |        |      0 |\n|   2 |   VIEW                |           |      1 |        |      2 |\n|   3 |    SEQUENCE           | SEQ_SALES |      1 |        |      2 |\n|*  4 |     HASH JOIN OUTER   |           |      1 |  50221 |      2 |\n|   5 |      TABLE ACCESS FULL| SALES_SRC |      1 |  50221 |  50221 |\n|   6 |      TABLE ACCESS FULL| SALES_TGT |      1 |    918K|     26 |\n----------------------------------------------------------------------\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 3: No DML restart in Oracle 23ai<\/em><\/strong><\/p>\n\n\n\n<p>However, it is a little confusing what I can see doing this test in my Always Free Autonomous Data Warehouse instance running Oracle 23.8 (also checked with 23.7). As you can see in Listing 4, ORA-14359 is thrown instead of ORA-30926 and we can see a restart again. First, I see this as evidence that these two errors belong together and originate in the new version from a common predecessor. Second, using this new error ORA-14359 in such a situation cannot be intentional from my point of view. This message does not describe well what the reason was and, in fact, it is the old behavior leading to a restart. I hope this will be fixed soon.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nBANNER_FULL                                                                     \n--------------------------------------------------------------------------------\nOracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and\n Engineered Systems\nVersion 23.8.0.25.05                                                           \n                                                                                \n1 row selected.\n\nMERGE \/*+ gather_plan_statistics *\/ INTO sales_tgt s\nUSING sales_src a\nON ( a.sale_id =  s.sale_id  \nWHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold\nWHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id, \n                              time_id, amount_sold)\n        VALUES (seq_sales.nextval, a.prod_id, a.cust_id, \n                a.time_id, a.amount_sold)\nError at line 7\nORA-14359: The operation was unable to obtain a consistent set of rows that needed to be modified.\n\n----------------------------------------------------------------------\n| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |\n----------------------------------------------------------------------\n|   0 | MERGE STATEMENT       |           |      3 |        |      0 |\n|   1 |  MERGE                | SALES_TGT |      3 |        |      0 |\n|   2 |   VIEW                |           |      3 |        |  50131 |\n|   3 |    SEQUENCE           | SEQ_SALES |      3 |        |  50131 |\n|*  4 |     HASH JOIN OUTER   |           |      3 |  50127 |  50131 |\n|   5 |      TABLE ACCESS FULL| SALES_SRC |      3 |  50127 |    150K|\n|   6 |      TABLE ACCESS FULL| SALES_TGT |      3 |    918K|    918K|\n----------------------------------------------------------------------\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 4: ORA-14359 in Always Free Autonomous Data Warehouse <\/em><\/strong><\/p>\n\n\n\n<p>In the next post I will check whether there are changes in how MERGE is handling a write consistency and statement restarts.<\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n<div class=\"crp-list-container\"><h3 class=\"crp-list-title\">Related Posts<\/h3><ul class=\"crp-list\"><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-dml-returning-clause-in-oracle-23ai\/\" target=\"_blank\">MERGE and DML RETURNING clause in Oracle 23ai<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926\/\" target=\"_blank\">MERGE and ORA-30926<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/merge-and-ora-30926-part-2-or-differences-in-write-consistency-between-update-and-merge\/\" target=\"_blank\">MERGE and ORA-30926 &#8211; Part 2 or differences in write consistency between update and merge<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>One of the topics I&#8217;ve found very interesting as I started blogging was the sometimes strange behavior of the MERGE statement, the reasons for ORA-30926 and what it has to do with write consistency. My first blog post about it was almost exactly ten years ago. Time for a follow up! Especially because there are [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2496,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,2,3],"tags":[158,159,6,160,27],"class_list":["post-1840","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-merge","category-oracle","category-sql","tag-23ai","tag-dml-restart","tag-merge","tag-ora-14359","tag-ora-30926"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1840","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=1840"}],"version-history":[{"count":30,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1840\/revisions"}],"predecessor-version":[{"id":2498,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1840\/revisions\/2498"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/2496"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}