Some code shows how to manage transaction in multiple database system


we create a table test_log in remote database, and a procedure test_log_pkg.insert_log to insert log into this table

create or replace package body test_log_pkg is
procedure insert_log(pvs_log in varchar2) is
begin
insert into test_log values (pvs_log);
end;
end test_log_pkg;


we create a procedure test_dblink_pkg.insert_remote_log in local database which invoke the insert_log in remote database by database link. we added PRAGMA AUTONOMOUS_TRANSACTION in declaration section, and commit the insert at the end.

create or replace package body test_dblink_pkg is

procedure insert_remote_log(pvs_log IN varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert_log@remote_db(pvs_log);
commit;
END;

end test_link_pkg;

then we create a table test_log in local database, and we create a test procedure which insert log into test_log in local database, and
invoke insert_remote_log to insert log into test_log in remote database, at the end, we rollback all of operations.

procedure test(pvs_log IN varchar2) is
BEGIN
insert into test_log values(pvs_log);
insert_remote_log(pvs_log);
rollback;
END;

after we run test, test_log in remote database has a new record, but test_log in local database has nothing.
It shows that the transaction for local database is rolled back, but the transaction for remote database is commited