Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-02041 client database did not begin a transaction

ORA-02041 client database did not begin a transaction

  • November 10, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

ORA-02041 client database did not begin a transaction

Cause: An update occurred at a coordinated database without the coordinator beginning a distributed transaction. This may happen if a stored procedure commits and then performs updates, and the stored procedure is invoked remotely. It could also happen if an external transaction monitor violates the XA protocol.

Action: If the cause is the former, check that any commit is not followed by an update.

ORA-02041 occurs when we are trying to execute a stored procedure using database link, which perform some dml operation then commits and then again tries to perform another

I am here trying reproduce ORA-02041 with a very simple example. Here we have 2 database,

1) "RemoteDB" which is having a table "remote_table" and a proc "remote_proc" which performs some dml.

2)"LocalDB" which is having a db link "remote" and a proc "local_proc" executing "remote_proc" multiple times.

On RemoteDB, lets create required objects.

SQL> create table remote_table
  2  (
  3  id number,
  4  name varchar2(100)
  5  );
Table created.

SQL> create or replace procedure remote_proc
  2  (
  3     p_id remote_table.id%type,
  4     p_name remote_table.name%type
  5  )
  6  is
  7  begin
  8     insert into remote_table (id) values(p_id);
  9     commit;
 10     update remote_table set name = p_name
 11     where id = p_id;
 12  end;
 13  /
Procedure created.

On LocalDB, lets create its procedure, assuming "remotedb" database link is already created.

SQL> create or replace procedure local_proc
  2  is
  3  begin
  4     nss.remote_proc@remotedb(1,'Amit');
  5     commit;
  6     nss.remote_proc@remotedb(2,'Nimish');
  7     commit;
  8     nss.remote_proc@remotedb(3,'Rahul');
  9     commit;
 10     nss.remote_proc@remotedb(4,'Sachin');
 11     commit;
 12     nss.remote_proc@remotedb(5,'Anuj');
 13     commit;
 14     nss.remote_proc@remotedb(6,'Puru');
 15     commit;
 16  end;
 17  /
Procedure created.

Now lets try to execute "local_proc"

ORA-02041