今天在做etl加载历史数据,处理到8月份的时候,报错 ORA-30926: unable to get a stable set of rows in the source tables,该etl过程中有用merge语句,google该错误的解释:
Oracle Error :: ORA-30926
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.
non-deterministic 非确定性的到底指的是什么呢?再搜
ORA-30926: unable to get a stable set of rows in the source tables" error if there is either a many-to-one or many-to-many relationship between the source and target tables. This is not as serious as it sounds because you would normally have to MERGE a one-to-one or one-to-zero relationship as your join condition would be protected by the target's primary key.
When performing a merge statement, the table to be merged had multiplerecords with the same key used for matching. While this is ok forrecords to be inserted into the target table, Oracle doesn't like thison the update portion of the statement. The solution is to remove the
duplicate or pick a matching key that is truely unique.
此处说明了,merge在做update子句的时候,违反target的主键约束可能会导致ora-30926 错误。
找出procedure,查看source,和target,结果看到source的确有重复记录,而且在清洗的时候没能发现这个bug。
好了,可以手工把重复记录删除,至少不影响历史资料的加载了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10159839/viewspace-364685/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10159839/viewspace-364685/