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: 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.
any non-deterministic where clauses and reissue the dml.
question was asked, concerning a user's SQL, and his confrontation with
I have 2 DB both are
184.108.40.206.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 email@example.com sta,
> 6 firstname.lastname@example.org sct,
> 7 email@example.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.
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."