EXPERT RESPONSE
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 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.
|
