Updating data in remote database when local database is updated

The first thing you need to do is to create a database link pointing to the other location. The database link can be created with a command similar to the following:

CREATE DATABASE LINK other_db CONNECT TO scott IDENTIFIED BY tiger

USING 'tns_alias';

You'll want to give the database link a better name, use the appropriate userid/password to connect to the remote database, and configure your TNSNAMES.ORA file with a TNS alias to point to that database. You can test the database link with a simple query like the following:

SELECT sysdate FROM [email protected]_db;

The "@other_db" clause uses the database link you created. If the query returns the date, then the link works properly. Once the link is set up, you can either issue a command to modify the data in the remote database when you modify the local database, or you can use a database trigger. For instance, if the application modifies the local database by performing an INSERT similar to the following,

INSERT INTO my_table VALUES (1,'Two');

then you can also code another SQL statement to insert the same values across a database link.

INSERT INTO [email protected]_db VALUES (1,'Two');

Additionally, you can create a trigger:

CREATE TRIGGER modify_remote_my_table
BEFORE INSERT ON my_table
BEGIN
  INSERT INTO [email protected]_db VALUES(:new.colA, :new.colB);
END;
/

You can write similar triggers for updating and deleting rows as well.