ORA-02055: distributed update operation failed; rollback req

Source: Internet
Author: User
Tags savepoint

Recently, my colleague encountered a strange problem for help. The following is the reproduction and solution of the problem of foreign gods

-------------------------------------------------- -------------------------------------------------- --------------------------

ORA-02055: distributed update operation failed; rollback required

-------------------------------------------------- ------------

You call a remote procedure or package over a database link.

Now, on the other side (the remote side), there was an error encountered, but there were already some statements executed successfully.

Now you will need to perform a rollback before you can do a select on the calling side, or you will get this error.

Reason: Calling a remote stored procedure or package through a database link. However, the remote database reported an error during execution, but some statements have been successfully executed

Measures: Before the calling terminal can query, you need to perform a rollback operation, otherwise you will get the above error prompt.

The problem reappears:

-First we create some test users

SQL> drop user test1 cascade;

User dropped.

SQL> drop user test2 cascade;

SQL> create user test1 identified by test1;

User created.

SQL> create user test2 identified by test2;

User created.

SQL> grant create session, create table, create trigger, create procedure, create database link to test1, test2;

Grant succeeded.

SQL> alter user test1 quota unlimited on system;

User altered.

SQL> alter user test2 quota unlimited on system;

User altered.
Now we connect to test 2 (remote user) and create a table

SQL> conn test2 / test2 @ XE
Connected.

SQL> create table test2_tab (n number);

Table created.

SQL> insert into test2_tab values (1);

1 row created.

SQL> commit;

Commit complete.
In order to demonstrate this error, we create a trigger on the newly created table, but make sure the trigger fails. In our case, we assign a character to a number field:

create or replace trigger test2_tab_bir
before insert on test2_tab
for each row
begin
  : new.n: = 'a';
end;
/

Trigger created.
Connect to test1, create database link

SQL> conn test1 / test1 @ XE
Connected.
SQL>

SQL> create database link test2 connect to test2 identified by test2 using 'XE';

Database link created.
Now we will create a procedure which will first do a successful dml, after that a dml that fails due to the incorrect trigger:

create or replace procedure p is
begin
  -first do a statement that executes ok
  update test2_tab @ test2 set n = 2;
  -next statement will fail because of the invalid trigger
  insert into test2_tab @ test2 values (1);
end;
/

Procedure created.
Call the procedure, it will fail:

SQL> exec p
BEGIN p; END;


ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-06502: PL / SQL: numeric or value error: character to number conversion error
ORA-06512: at "TEST2.TEST2_TAB_BIR", line 2
ORA-04088: error during execution of trigger 'TEST2.TEST2_TAB_BIR'
ORA-02063: preceding 3 lines from TEST2
ORA-06512: at "TEST1.P", line 4
ORA-06512: at line 1
Now we have a failed distributed transaction, you need to rollback, else you will get the error when selecting ANY table / view

SQL> select sysdate from dual;
select sysdate from dual
                     
ERROR at line 1:
ORA-02067: transaction or savepoint rollback required

-also happens when oracle itself calls another select recursively (notice the ORA-00604)

SQL> select * from user_2pc_pending;
select * from user_2pc_pending
               
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
-----------------------------------

Present By By Dylan.


————————————————
Copyright statement: This article is an original article by CSDN blogger "Lohan Han", following the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement for reprint.
Original link: https://blog.csdn.net/IndexMan/java/article/details/9001812

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.