Distributed Query across users

Question

I currently have 3 Oracle database users who each replicated tables. I was just wondering how could I go about updating a single variable across all these users in a transaction? i.e. these 3 users have a table of values and one tuple needs to be updated across all 3 users to maintain consistency. I am using Oracle SQL server and just need to know how I could go about performing a single SQL transaction to update all user values at the same time. Thanks!

Answer 1

One possibility is that you use a stored procedure to update the three tables, with and EXCEPTION block to catch errors with ROLLBACK so that, if there is an error, the updates will be annulled.
Here is an example. At the end the code has been stopped.
I have intentionally made the column in table_3 varchar(9) and not varchar(10) to provoke an error only on the third table.
The last query doesn't find any results. I believe that this is because DBfiddle takes rollback to cancel the whole script, event though several seperate blocks. This and the error messages will have to be finalised by someone who has access to a 'real' Oracle database and not an online simulator.

NB I'm sure that it's not the password that you'll be changing : this is just for the sake of an example.

Another possibility would be to create a separate table for the shared columns and use a view per user to regroup the information from the users own table and the shared table. This depends on the exact need which I don't have.

CREATE TABLE user_1 (password varchar(10));
CREATE TABLE user_2 (password varchar(10));
CREATE TABLE user_3 (password varchar(9));

create procedure updateUsers
   ( new_p in varchar2)
as
begin
SAVEPOINT before;
  update user_1 set password = new_p;
  update user_2 set password = new_p;
  update user_3 set password = new_p;
EXCEPTION WHEN OTHERS THEN 
   DBMS_OUTPUT.PUT_LINE(
         'Some other kind of error occurred.');
   ROLLBACK;
end;
/

insert into user_1 values ('initial');
insert into user_2 values ('initial');
insert into user_3 values ('initial');

select password from user_1
UNION ALL
select password from user_2
UNION ALL
select password from user_3;

| PASSWORD |
| :------- |
| initial  |
| initial  |
| initial  |

begin
  updateUsers('new_pass') ;
end;
/

select password from user_1
UNION ALL
select password from user_2
UNION ALL
select password from user_3;

| PASSWORD |
| :------- |
| new_pass |
| new_pass |
| new_pass |

begin
  updateUsers('password_3') ;
end;
/

select password from user_1
UNION ALL
select password from user_2
UNION ALL
select password from user_3;

| PASSWORD |
| :------- |

db<>fiddle here