locked
How to synchronize data of different oracle database ? RRS feed

  • Question

  • User1339587304 posted

    Hi all

    I am using oracle database,

    Database db1          

    Table:   tlbdb1_City1

     

    Database db2 

    Table:   Tlbdb2_City2

     

    I want to synchronize data of tlbdb1_City1 to Tlbdb2_City2 

    How it is possible, change of tlbdb1_City1 is automatically update at Tlbdb2_City2 

     

    Regards

    <st1:personname w:st="on">Anup</st1:personname>

    Tuesday, November 17, 2009 3:52 AM

All replies

  • User656402446 posted

    you can use a database link for this. please refer these links

    http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_5005.htm

    http://download-west.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_concepts002.htm


    Tuesday, November 17, 2009 4:50 AM
  • User364663285 posted

    Hi all

    I am using oracle database,

    Database db1          

    Table:   tlbdb1_City1

     

    Database db2 

    Table:   Tlbdb2_City2

     

    I want to synchronize data of tlbdb1_City1 to Tlbdb2_City2 

    How it is possible, change of tlbdb1_City1 is automatically update at Tlbdb2_City2 

     

    Regards

    Anup

     

    create update/insert triggers on tlbdb1_City1 to update Tlbdb2_City2 by the DB links

    http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_5005.htm

    Thursday, November 19, 2009 3:20 AM
  • User-2098386135 posted

    You can also use materialize views....

     

    http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repmview.htm 

    Monday, November 30, 2009 4:31 PM
  • User364663285 posted

    Hi all

    I am using oracle database,

    Database db1          

    Table:   tlbdb1_City1

     

    Database db2 

    Table:   Tlbdb2_City2

     

    I want to synchronize data of tlbdb1_City1 to Tlbdb2_City2 

    How it is possible, change of tlbdb1_City1 is automatically update at Tlbdb2_City2 

     

    Regards

    Anup

    you can have 3 triggers for update/delete/insert on tlbdb1_City1 to automatically apply the change to Tlbdb2_City2

    Here is one example
    An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

    The syntax for an AFTER INSERT Trigger is:

    CREATE or REPLACE TRIGGER trigger_name
    AFTER INSERT
        ON table_name
        [ FOR EACH ROW ]
    DECLARE
        -- variable declarations
    BEGIN
        -- trigger code
    EXCEPTION
        WHEN ...
        -- exception handling
    END;

    trigger_name is the name of the trigger to create.

    Restrictions:

    •You can not create an AFTER trigger on a view.
    •You can not update the :NEW values.
    •You can not update the :OLD values.


    For example:

    If you had a table created as follows:

    CREATE TABLE orders
    ( order_id number(5),
     quantity number(4),
     cost_per_item number(6,2),
     total_cost number(8,2)
    );


    We could then create an AFTER INSERT trigger as follows:

    CREATE OR REPLACE TRIGGER orders_after_insert
    AFTER INSERT
        ON orders
        FOR EACH ROW

    DECLARE
        v_username varchar2(10);

    BEGIN
        -- Find username of person performing the INSERT into the table
        SELECT user INTO v_username
        FROM dual;

        -- Insert record into audit table
        INSERT INTO orders_audit
         ( order_id,
           quantity,
           cost_per_item,
           total_cost,
           username )
        VALUES
         ( :new.order_id,
           :new.quantity,
           :new.cost_per_item,
           :new.total_cost,
           v_username );

    END;

    Tuesday, December 1, 2009 1:19 AM
  • User-331346503 posted
    i use materialize views for this purpose and oracle jobs to schedule the synchronization/refresh work.
    Tuesday, December 1, 2009 1:30 AM
  • User1839668097 posted

    You can do this with dbForge Data Compare for Oracle tool for data comparison and synchronization issues. Also this ability is available in more feature-rich dbForge Studio.

    Tuesday, January 24, 2017 11:56 AM