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
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 dual@other_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 my_table@other_db VALUES (1,'Two');
Additionally, you can create a trigger:
CREATE TRIGGER modify_remote_my_table BEFORE INSERT ON my_table BEGIN INSERT INTO my_table@other_db VALUES(:new.colA, :new.colB); END; /
You can write similar triggers for updating and deleting rows as well.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading