Parallel degree affecting MERGE!! -- ORA-30926

From: Dexter <vikram_ramakrishnan_at_ieee.org>
Date: 21 Jun 2005 10:24:41 -0700
Message-ID: <1119374681.146159.165540@f14g2000cwb.googlegroups.com>

Hi,

I'm running oracle 9i (9.2.0.5) on HP-UX. I've offlate started seeing the following error when doing a MERGE.

DBD::Oracle::db do failed: ORA-12801: error signaled in parallel query server P019
ORA-30926: unable to get a stable set of rows in the source tables

This the query that i'm running.

merge --+ parallel(dst,4) parallel(src,4) into a.edet dst
using a.edet_at_MATRIX_TO_ZION src on (
dst.eid = src.eid
) when matched then update set

....Update the columns....
when not matched then insert (
dst.eid,
...all teh other columns...
)

values
(
src.eid,
...all teh other columns...
)

I have an unique index on column eid on both the source and target tables.(There are no duplicates to cause 30926). The Source and destination tables are NOT partitioned and have a dba_tables degree of 1.

The interesting thing is when i change the query to either not have a parallel hint or to "parallel (dst,1) parallel (src,1)" it executes fine.

Can anyone explain this and let me know the root cause of this issue.

-Thanks.
Vikram. Received on Tue Jun 21 2005 - 12:24:41 CDT