Q
Problem solve Get help with specific problems with your technologies, process and projects.

Partial database replication setup

How do I set up a partial replication from one database to another?

I am interested in setting up a partial replication from one database to another. I have never done this before and would like some assistance in setting this up. Thanks.
Oracle has a product called Replication Services which performs this type of replication. This product costs additional funds which may not be justifiable for replicating a small subset of your database. If that is the case, then you can write your own replication routines using database triggers and database links.
  1. Create the database link to the remote database:
    CREATE DATABASE LINK link_name CONNECT TO 
    username IDENTIFIED BY password USING 'tns_alias';
    
  2. Create a trigger on the table which populates newly inserted rows into the remote database table.
    CREATE TRIGGER my_table_insert_trig
    BEFORE INSERT ON my_table
    BEGIN
       INSERT INTO my_table@link_name VALUES 
       (:new.colA, :new.colB, ..., :new.colX);
    END;
    /
    
You'll have to create your own triggers similar to the above which perform your replication for you.
This was last published in July 2006

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close