ORA-30926: unable to get a stable set of rows in the source tables tips Oracle Error Tips by Stephanie F.

 

The Oracle docs note this on the ora-30926 error:

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