Tough one, given that you have no access beyond a SELECT in db_A. So here's a thought, but it requires some pretty strict assumptions that may (or may not) be met:
- All tables being sync'd have either:
- A timestamp (the more resolution the better)
- A unique, sequential ID
- All table rows, once sync'd, don't change.
- Alternatively, if a change does occur AND updates a timestamp on the record, you might be able to work it out that way.
Now, on db_B:
CREATE TABLE table1...
CREATE TABLE table2...,
PROCEDURE SYNC_TABLE1 IS
SELECT MAX(SEQUENCE_NO), MAX(ACTIVITY_DATE)
INTO MAX_SEQUENCE_NO, MAX_ACTIVITY_DATE
EXCEPTION WHEN NO_DATA_FOUND THEN
MAX_SEQUENCE_NO := 0;
MAX_ACTIVITY_DATE = TO_DATE('01/01/1980', 'MM/DD/YYYY');
-- Bring over recent entries from db_A.table1 to db_B.table1
INSERT INTO table1
-- if using timestamps as your criteria:
activity_date > MAX_ACTIVITY_DATE
-- if using sequence nos as your criteria:
sequence_no > MAX_SEQUENCE_NO
-- consider adding limiters to decrease the bandwidth necessary
-- for large transactions. For example activity_date < MAX_ACTIVITY_DATE + 30
-- would load a month's worth of transactions at a time. sequence_no <
-- MAX_SEQUENCE_NO + 500 would load 500 transactions at a time.
EXCEPTION WHEN OTHERS THEN
-- Consider logging the error!
(lather, rinse, repeat.)
Again, this only works if you have either a sequential unique ID OR an activity date that is always updated on db_A (and that date should be of sufficient resolution to detect one transaction inserted a millisecond after the previous one, so timestamps are best.)
The way I synchronize data between Oracle instances (and non-Oracle instances, e.g., Oracle to mySql) is to make sure I have a sync_date column on all my sync'able tables. When a request is made to sync data, that sync_date column is filled in with the date of the sync. Therefore the actual sync process is simple:
FOR r in ( SELECT * FROM table1
WHERE sync_date IS NULL ) LOOP
SET sync_date = current_timestamp
Usually a limiter goes into effect, but you get the idea. Furthermore, if data changes on a record, the sync_date column is NULLed, at which point the sync process will pick it back up again.
Note: no matter the situation, you will need some sort of de-duplication handling if you are able to support data changes once a row has been sync'd. You could try a MERGE, or an INSERT with a WHERE NOT EXISTS on the SELECT clause coupled with an UPDATE ... WHERE EXISTS.
Hopefully that helps.