April 29th, 2013, 10:58 AM
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
April 30th, 2013, 08:34 AM
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...
Originally Posted by Xcrypted
Just a tip in your query you can specify from which database you get a data
insert into us..staff
from uk..staff k
left join us..staff s
where s.staff_id is null
April 30th, 2013, 09:23 AM
Thanks for the reply. After hours of trial and error, the below code is what did the trick.
PROMPT Creating database lint to uk (UK)
DROP DATABASE LINK UK_LINK
/CREATE DATABASE LINK UK_LINK
CONNECT TO *** IDENTIFIED BY ******
DROP SNAPSHOT CLIENTS_TABLES_SNAP
PROMPT Creating database replication schedule for clients table
CREATE SNAPSHOT CLIENTS_TABLE_SNAP
START WITH TO_DATE(to_char(sysdate, ‘DD-MON-YYYY’) || ‘-11:59’, ‘DD-MON-YYYY-
NEXT TO_DATE(to_char(sysdate, ‘DD-MON-YYYY’) || ‘-11:50, ‘DD-MON-YYYY-HH-
MI’) + 1
AS SELECT * FROM clients@UK_LINK