ORA-02064: distributed operation not supported

[mix_dba90]

Hi,

We are encountering with error of ORA-02064: distributed operation not supported.Our operation failed due to this error. Recently we changed our database version. Before this, it was running fine and after upgrading with newer version of Oracle database, we are encountering with this error. Is there any solution to avoid this ORA error?

Thanks a lot in Advance.

[coolguy]

Can you provide us more detail of operation and database versions. Distributed transaction means you are trying to perform any distributed transaction using database link. Then kindly provide us more detail of target database version too.

[mix_dba90]

Hi Coolguy,

We are performing distributed transaction from Oracle 10.2.0.2 and our target database version is 10.2.0.4, which was upgraded recently. We loaded one temp table in 10.2.0.2 and trying to insert all rows in target database. During this operation we got this error ORA-02064.

[Denis]

Can you tell me which insert statement you are using? Simple insert statement then are you using any sub-query in INSERT statement?

[mix_dba90]

Denis,

We are using MERG SQL command in insert statement.

[shailesh_dba]

Hi,

Try to execute same MERGE statement in reverse order. Like at present you are executing MERGE statement from oracle 10.2.0.2 and using database link you are inserting data to Oracle 10.2.0.4. Then try to reverse it for testing purpose. Execute same MERGE statement from Oracle 10.2.0.4 and using database link you can able to insert in Oracle 10.2.0.2 database. Check does it executed or not. I think you are encountering with Oracle Bug.

[mix_dba90]

Hi Shailesh,

It is working. When I tried execute merge statement from 10.2.0.4 to Oracle 10.2.0.2, it is working. Can you tell me what is the issue and how to resolve this issue?

[gautam123]

Hi Mix,

It Oracle Bug 4311273 ORA-2064 using MERGE statement over a database link. Your Oracle database version 10.2.0.2 is affected with this bug. If you upgrade it to Oracle 10.2.0.4 then bug is resolved in same.

You should need to upgrade Oracle database or you need to change your coding and avoid to use MERGE statement with database link access in distributed transaction.