Oracle Error Code 30926
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
Ora-30926 Ask Tom
Us Learn more about Stack Overflow the company Business Learn more about hiring how to resolve ora-30926 developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Non-deterministic Where Clauses Oracle
the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORA-30926: unable to get a ora-30926 techonthenet stable set of rows in the source tables up vote 78 down vote favorite 13 I am getting ORA-30926: unable to get a stable set of rows in the source tables 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 ( ora-39126: worker unexpected fatal error in kupw$worker.stats_load [marker] a.ROWID = src.row_id ) WHEN MATCHED THEN UPDATE SET in_correct = 'Y'; I've ran table_1 it has data and also I've ran the inside query (src) which also has data. Why would this error come and how can it be resolved? oracle sql-merge share|improve this question edited Mar 10 at 9:45 diziaq 2,06981532 asked Feb 25 '10 at 20:13 Omnipresent 10.6k33108161 add a comment| 4 Answers 4 active oldest votes up vote 117 down vote accepted 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: 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';
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Ora-30926 Merge Update
ORA-30926: unable to get a stable set of rows in the source tables tips unable to get a stable set of rows in the source tables merge statement Oracle Error Tips by Stephanie F. The Oracle docs note this on the ora-30926 error: ORA-30926: unable to get a
Ora 30926 Impdp
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 http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables reissue the dml. The following question was asked, concerning a user's SQL, and his confrontation with ORA-30926. I have 2 DB both are 9.2.0.4.0. Database A connect to Database B via db link e21prd.nwt.com. But I execute following SQL on Database A, ora-30926 display: > 1 UPDATE dm_cus_customer cus > 2 SET cus.returnmail = 'Y' > 3 WHERE cus.cust_id in > 4 ( SELECT cua.cust_id > 5 FROM sta_special_treatmnt_associate@e21prd.nwt.com sta, > 6 sct_special_cust_treat_master@e21prd.nwt.com sct, > http://www.dba-oracle.com/t_ora_30926_unable_to_get_a_stable_set_of_rows_in_the_source_tables.htm 7 cua_customer_account@e21prd.nwt.com cua > 8 WHERE sta.treatment_id = sct.treatment_id > 9 AND sct.treatment_cd = 'RETURNMAILCUA' > 10 AND sct.lvl_ind = 'CUA' > 11 AND sta.lvl_ind = 'CUA' > 12 AND sta.sts = 'A' > 13 AND TRUNC(sta.eff_strt_date) <= TRUNC(SYSDATE) > 14 AND NVL(sta.eff_end_date,sysdate) >= TRUNC(SYSDATE) > 15 AND sta.cua_cus_id = cua.cust_acc_id > 16 ) > 17* AND cus.returnmail IS NULL > SQL> / > AND sta.cua_cus_id = cua.cust_acc_id > * > ERROR at line 15: > ORA-30926: unable to get a stable set of rows in the source tables If I remove line 7 and 15. The SQL is no problem. If I run the subquery (line4-16) first, put the result on line 3 as the where clause. The SQL also no problem. An Oracle responder offered this information regarding ORA-30926: ORA - 30926 Error occurs when a stable set rows are not found for a large dml activity or a non-deterministic Where Clause. They also suggested that the user try to write, "a query using the where clause in 15th line, and update the Thread. It may be non-deterministic." �� Burleson is the American Team Note: This Oracle documentation was created as a support and Orac
set of rows in the sourcetables Posted on January 29, 2013 by Aykut Akin In my recent project I get this error while I'm using Merge in Oracle. After some searching I found the problem and decided to write a post about https://aykutakin.wordpress.com/2013/01/29/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables/ it:) The reason of this error is source table. In merge queries we have source table and target table. We make some operations (Update, Insert, Delete) according to source table. We connect target table and source table in ON clause of Merge. And merge expects that source table returns unique values according to ON clause. If your columns, that you use in the ON clause, don't provide unique key feature, you will get this oracle error error too.. Don't forget primary key is a unique key;) I think this is reasonable expectation. Think about it: what happens if source table returns non-unique values? Which data should be used to update rows? How can Oracle select this? So this is why Oracle expect uniqueness in On clause. Let me make you see this error with example: CREATE TABLE source_table ( col1 NUMBER, col2 VARCHAR2(10), col3 VARCHAR2(10) ); INSERT INTO source_table (col1, col2, oracle error code col3) VALUES (1, 'a', 'w'); INSERT INTO source_table (col1, col2, col3) VALUES (1, 'b', 'x'); INSERT INTO source_table (col1, col2, col3) VALUES (2, 'c', 'y'); INSERT INTO source_table (col1, col2, col3) VALUES (3, 'c', 'z'); COMMIT; CREATE TABLE target_table ( col1 NUMBER, col2 VARCHAR2(10), col3 VARCHAR2(10) ); INSERT INTO target_table (col1, col2, col3) VALUES (1, 'b', 'z'); INSERT INTO target_table (col1, col2, col3) VALUES (3, 'd', 'w'); COMMIT; In above queries we just simply create sample data. Now we are going to merge two table. MERGE INTO target_table trg USING (--Actually we can simply write source_table for this example but I want to write Select:) SELECT col1, col2, col3 FROM source_table ) src ON (trg.col1 = src.col1) WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause trg.col2 = src.col2, trg.col3 = src.col3 WHEN NOT MATCHED THEN INSERT ( col1, col2, col3 ) VALUES ( src.col1, src.col2, src.col3 ); COMMIT; Aaand boom! We get the error! We used col1 column for merge operation. And in our source table col1 is not unique. We have two different rows in source_table that col1 column is '1'. So our merge query couldn't decide the which one has desirable value. (1,'a','w') OR (1,'b',x')? You couldn't decide it too, didn't you:) Now let's make correct this merge query. I'm looking rows