Ora-30926 Error In Oracle
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter ora-30926 ask tom a title. You can not post a blank message. Please type your
How To Resolve Ora-30926
message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion non-deterministic where clauses oracle is archived 13 Replies Latest reply on Feb 23, 2013 4:18 PM by Peter Gjelstrup MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source 959406 Feb
Ora-30926 Techonthenet
21, 2013 1:57 PM hi all transactional table issue both equal condition(ie on clause common row not any) not avilable what can i do_+ in this merge statement CORM_IRT_INV_RESOURCE_Tmp and PEG6_WRDT_RES_DTS_TMP (both temp/transaction table) . error are display oracle - ORA-30926: unable to get a stable set of rows in the source i ask both table common any thing AVAILABLE USING ora-39126: worker unexpected fatal error in kupw$worker.stats_load [marker] on clause ANY SOLUTION PLZ PROVIDE ME MERGE INTO CORM_IRT_INV_RESOURCE_Tmp A USING (SELECT DISTINCT * FROM PEG6_WRDT_RES_DTS_TMP) B ON ( B.WRDT_GUID = A.IRT_GUID AND B.WRDT_DOCUMENT_NO = A.IRT_ORDER_NO AND B.WRDT_DOCUMENT_OU = A.IRT_ORDER_OU AND B.WRDT_REF_LINE_NO = A.IRT_REF_LINE_NO AND B.WRDT_TASK_LINE_NO = A.IRT_LINE_NO AND B.WRDT_RESOURCE_NO = A.IRT_RESOURCE_CODE AND B.WRDT_RES_TYPE = A.IRT_RESOUCE_TYPE AND A.IRT_GUID = v_Guid_Tmp AND A.IRT_ORDER_NO = v_cusorderno_Tmp AND A.IRT_ORDER_OU = v_COrderOU_Tmp) WHEN MATCHED THEN UPDATE SET A.IRT_NORMALRATE_PERHR = B.WRDT_RATE_PER_HR, A.IRT_OTRATE_PERHR = B.WRDT_OVERTIME_RATE_PER_HR, A.irt_facilityrate_perhr = b.wrdt_std_fac_rate, A.irt_resource_price = --sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_NO, 0) * NVL(B.WRDT_RATE_PER_HR, 0), v_pamt_tmp) + sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_OT, 0) * NVL(B.WRDT_OVERTIME_RATE_PER_HR, 0), v_pamt_tmp), round(NVL(IRT_BILLABLE_HR_NO, 0) * NVL(B.WRDT_RATE_PER_HR, 0), v_pamt_tmp) + round(NVL(IRT_BILLABLE_HR_OT, 0) * NVL(B.WRDT_OVERTIME_RATE_PER_HR, 0), v_pamt_tmp), A.IRT_SERVICE_PRICELIST = B.WRDT_SER_PRLIST, A.IRT_REVISION_NO = B.WRDT_SER_PRL_REVNO, A.IRT_EFFECTIVE_TILLDATE = B.WRDT_SER_PRL_EFF_DATE, A.IRT_BILLABLE_MISCPRICE = B.wrdt_misc_cost; EXCEPTION WHEN OTHERS THEN NULL;*/ Edited by: 956403 on 21 Feb, 2013 5:55 AM I have the same question Show 0 Likes(0) 45217Views Tags: none (add) mergeContent tagged with merge, statementContent tagged with statement This content has been marked as final. Show 13 replies 1. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source Karthick2003 Feb 21, 2013 6:43 AM (in respo
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:Ora-30926 Merge Update
unable to get a stable set of rows in the source tables tips Oracle Error
Ora 30926 Impdp
Tips by Stephanie F. The Oracle docs note this on the ora-30926 error: ORA-30926: unable to get a stable set of ora-38104 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. https://community.oracle.com/thread/2503509 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, > 7 cua_customer_account@e21prd.nwt.com cua > 8 http://www.dba-oracle.com/t_ora_30926_unable_to_get_a_stable_set_of_rows_in_the_source_tables.htm 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 Oracle training reference for use by our DBA performance tuning consulting profe
example of the MERGE for an upload is in this earlier blog post. This is the wonderful error message, which doesn't seem http://blog.mclaughlinsoftware.com/2010/03/05/stable-set-of-rows/ to have meaning for many. The key is the non-deterministic where https://www.experts-exchange.com/questions/27944225/ORA-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables.html clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can't be matched uniquely against the target table in the ON clause. The ON clause is where the MERGE statment matches the source query's error in rows against the target table's rows. The best join condition in a MERGE statement is one between a surrogate primary and foreign key column. 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 ora-30926 error in WHERE clause. Action: Remove any non-deterministic WHERE clauses AND reissue the dml. The problem is very much like when you write what you think is a single-row subquery but find out it's actually a multiple-row subquery when it return an ORA-01422 error in Oracle. As a rule, I've found that using the DISTINCT operator in the source SELECT statement fixes it most of the time because the join isn't returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE clause. It also happens when the SELECT clause returns date-time data types like SYSDATE instead of date data types. A date-time can be converted by using the TRUNCate function like this: MERGE INTO TABLE_NAME target USING (SELECT ... , TRUNC(SYSDATE) AS creation_date FROM ...) SOURCE ON (target.primary_key_column = SOURCE.primary_key_column) WHEN MATCHED THEN UPDATE SET last_updated_by = SOURCE.last_updated_by , last_update_date = SOURCE.last_update_date WHEN NOT MATCHED THEN INSERT VALUES ( column_list_of_values ); Hope
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > ORA-30926: unable to get a stable set of rows in the source tables Want to Advertise Here? Solved ORA-30926: unable to get a stable set of rows in the source tables Posted on 2012-11-22 Oracle Database 1 Verified Solution 5 Comments 2,509 Views Last Modified: 2012-12-04 I am getting the following error during merge. The target table has data while the source table doesn't have that row in it. However, in such a case i should get 0 rows merged, why do i get the above error? 0 Question by:gram77 Facebook Twitter LinkedIn Google LVL 23 Best Solution bypaquicuba I ran your examples and this is what I get: table SOURCE created. table TARGET created. 1 rows inserted. 1 rows inserted. 1 rows inserted. 0 rows merged. So, I'm not getting the error you're Go to Solution 5 Comments Message Active 3 days ago Author Comment by:gram772012-11-22 Example: CREATE TABLE source ( a NUMBER, b NUMBER, c NUMBER, d NUMBER, CONSTRAINT pk_source PRIMARY KEY(a,b,c) USING INDEX ); CREATE TABLE target ( a NUMBER, b NUMBER, c NUMBER, d NUMBER, CONSTRAINT pk_target PRIMARY KEY(a,b,c) USING INDEX ); /////////// INSERT INTO target values (1,1,1,1); INSERT INTO target values (2,2,2,2); INSERT INTO target values (3,3,3,3); //////////// select * from source; select * from target; //////// --Merge causing the error: MERGE INTO target t USING (SELECT a, b, c, d FROM source) s ON (t.a = s.a AND t.b = s.b) WHEN MATCHED THEN UPDATE SET t.c = s.c, t.d = s.d WHEN NOT MATCHED THEN INSERT (a, b, c, d ) VALUES (s.a, s.b, s.c, s.d ); 0 LVL 16 Overall: Level 16 Oracle Database 14 Message Expert Comment by:Swadhin Ray2012-11-22 In the below link it explains about the issue when merges : http://blog.mclaughlinsoftware.com/2010/03/05/stable-set-of-rows/ 0 LVL 22 Overall: Level 22 Oracle Database 19 Message Active 2 days ago Expert Comment by:Henka2012-11-22 As an addition to the previous comment: I have change source and target, and it works :). You made mistake because you have inserted values into table target instead of table source. MERGE INTO source t USING (SELECT a, b, c, d FROM target) s ON (t.a = s.a AND t.b = s.b) WHEN MATCHED THEN UPDATE SET t.c = s.c, t.d = s.d WHEN NOT MATCHED THEN INSERT (a, b, c, d ) VALUES (s.a, s.b, s.c, s.