The first thing you need to do is to create a database link pointing to the other location. The database link can...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.