something I just thought of....
Your logic to sync must be something like:
T0 - log into source database;
T1 -
T2 - get NEW_DATE/TIME to be used for next sync;
T3 - get all rows in table(s) where timestamp >= DATE/TIME from last sync;
T4 - logout
Ok, you are worried about missing rows in oracle such that they were in flight transactions (modifications taking place during T1 basically)
They would have a timestamp T1, but you would have a timestamp T2 for the next retrieval and hence would "miss them"
If you change T2 to:
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)
from v$transaction;
what you'll have is the date time of either
a) right now if no one is doing a transaction (no chance of a missed record) or
b) the timestamp of the last time no one WAS doing an in flight transaction
sure -- it opens the window for dups (but you have that already -- the dup window is not a nano-second or even a second, it it the time it takes for T3 to complete which could be measurable) but it prevents you from missing anything and leaves your code intact.
Partager