ORA-02064: distributed operation not supported

[aspiringprog]

Hi all,

I have two tables residing on two different servers. I am trying to update table 1 with values from table 2 using a merge statement and I get this error. I am not sure what it means. Any help?

 

[hoinz]

oerr ora 2064
02064, 00000, "distributed operation not supported"
// *Cause: One of the following unsupported operations was attempted:
// 1. array execute of a remote update with a subquery that references
// a dblink, or
// 2. an update of a long column with bind variable and an update of
// a second column with a subquery that both references a dblink
// and a bind variable, or
// 3. a commit is issued in a coordinated session from an RPC procedure

// call with OUT parameters or function call.
// *Action: simplify remote update statement

If this doesn't answer your question, then please help us to help you, e.g. by showing the statement that caused this error, and maybe be a describe of the tables involved.

 

[aspiringprog]

Here is the statement that is causing all the trouble.

a - table in local db
b - table in remote db, accessed through db link

merge into a
using b
on (a.id = b.id)
when matched then
update
set a.spec = b.spec,
a.mctn = b.mctn

id is the primary key for table b.

when I run this statement, I get the distributed operation not supported error.

 

[Thargy]

As a first port of call, manually move the remote table on to the local server, and then attempt the merge.

If it works, then you know for sure that the remoteness, or something to do with it was causing the problem. If it fails, then your merge is flawed somehow. Once you're absolutely certain that the merge is ok, then go back to the remote location and try again.

divide and conquer!

T

Grinding away at things Oracular