Thank you very much for the answer - Added Question
Annie Philip, October 29, 2016 - 7:27 am UTC
Thank you very much for the answer. Is reading the source schema tables with data links and transferring with the stored proc to destination schema tables a bad idea. If so can you cite why you feel this is bad?
Annie
October 30, 2016 - 9:50 am UTC
Its often harder than you think to do this.
If there is an uncommmitted transaction on source, it can easily be missed by a home grown replication mechanism.
9am: source:
insert into T values ( "9:00:00am"); -- not committed
9:01am: target:
select * from source where date > "8:30am" (lets says thats the last refresh)
update control_table set last_refresh = 9:01am
commit;
9:02am: source
"commit"
So the source now has a row marked 9am, which the target never saw, and the next refresh will only get data from 9:01am, thus never seeing the source row.
Flashback
Duke Ganote, October 31, 2016 - 3:25 pm UTC
Even low-volume tables with hourly DIY copying would probably require flashback queries as of a certain PIT (point-in-time), especially if there are inter-table dependencies, no?
October 31, 2016 - 11:22 pm UTC
Yup. Do-it-yourself replication always *seems* easy until you have to do it :-)