Thread: Replication

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Replication


    Hello everyone,

    I am having a hard time finding the information I need and am hoping you all can help.

    I am using sql developer and I have a database named us that holds 5 tables and a database named uk that holds the same 5 tables.

    It is a demo online store with an item, client, order, orderdetails, and staff tables.

    I want to set up replication so that at 11:59pm every night, anything added on the uk database is replicated to the us database.

    Can someone please help me with the code to do this? I have everything else working great but replication information is fuzzy to say the least.

    Thank you in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    Originally Posted by Xcrypted
    Hello everyone,

    I am having a hard time finding the information I need and am hoping you all can help.

    I am using sql developer and I have a database named us that holds 5 tables and a database named uk that holds the same 5 tables.

    It is a demo online store with an item, client, order, orderdetails, and staff tables.

    I want to set up replication so that at 11:59pm every night, anything added on the uk database is replicated to the us database.

    Can someone please help me with the code to do this? I have everything else working great but replication information is fuzzy to say the least.

    Thank you in advance
    In my understanding both databases on the same server, if so you need to just create script to add records from uk to us database and schedule to run that script 11:59pm everyday...
    Just a tip in your query you can specify from which database you get a data
    something like

    Code:
    insert into us..staff
    select k.*
    from uk..staff k
    left join us..staff s
    on k.staff_id-s.staff_id
    where s.staff_id is null
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Thanks for the reply. After hours of trial and error, the below code is what did the trick.

    Code:
    Create_replication_US.sql
    PROMPT Creating database lint to uk (UK)
     
    DROP DATABASE LINK UK_LINK
    /CREATE DATABASE LINK  UK_LINK
    CONNECT TO *** IDENTIFIED BY ******
    USING ‘UK’
    /
     
    DROP SNAPSHOT CLIENTS_TABLES_SNAP
    PROMPT Creating database replication schedule for clients table
    CREATE SNAPSHOT CLIENTS_TABLE_SNAP
    REFRESH COMPLETE
    START WITH TO_DATE(to_char(sysdate, ‘DD-MON-YYYY’) || ‘-11:59’, ‘DD-MON-YYYY-
    HH-MI’)
    NEXT TO_DATE(to_char(sysdate, ‘DD-MON-YYYY’) || ‘-11:50, ‘DD-MON-YYYY-HH-
    MI’) + 1
    AS SELECT * FROM clients@UK_LINK

IMN logo majestic logo threadwatch logo seochat tools logo