Original: http://www.iteye.com/problems/77856 timing data from one database table to a table in another database, and how to handle duplicate data? The table structure must not be broken, but the temporary table if its own database is OK, the problem is that the Oracle database is the customer's database, you can not do any extra operations in his database it? Is there any better way to do it?
This is really more difficult.
First of all, you need to retrieve data from the client Oracle, because this 1-minute interval does not know what new data is added to the user's computer (most of the times the user uses another system to insert the data, and you do not have the program interface of the system), so you can only take it out and rely on your own program to synchronize the data.
The practice I have seen is:
Assuming that the user machine is a (Oracle), your Java application is deployed on the B machine, and the Sybase database is on the C machine.
(1) Install a memory database on machine B (with an open source H2 memory database)
(2) Get all the data of a (Oracle), if the B machine's memory is not large, then batch acquisition can also, and B machine on the memory database in the temporary storage table temp to do a hash join, to obtain incremental data.
(3) Synchronize the incremental data to the B machine's memory database and the C machine's Sybase.
Here, the need to synchronize the table is to be re-established every once in a while, such as the day table, month table, etc., so a table is not super large, to the point where the memory can not be digested, so this memory database of the temporary table also has a certain capacity.
There may be a better way, however, the intermediate table or intermediate file record should still be needed.
Periodically store data from one database table to a table in another database, and how do you handle duplicate data?