In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked?
My tables typically have a column
update_date DATE that holds the date/time last updated, and I am currently using that. I'm finding this isn't fully reliable, as some other stored procedure could insert data with this column set properly via
SYSDATE, then commit the transaction a few seconds later when it completes. If I ran a query after the DATE column was written but before the transaction was committed, my query will contain rows with later update_date values (by a second or two, usually) but I won't see the newest rows that have a short delay in being committed.
In other words, suppose in my first run I SELECT all rows, and the latest row I get has an
update_date of '2021-01-15 08:01:02'. Then my job keeps track of that value, and on my second run, the job now naively uses
WHERE update_date > :latest_changed_date_from_last_run, but such a query won't include data from '2021-01-15 08:01:02' that was committed after my last run, nor even perhaps data from '2021-01-15 08:01:01' or '2021-01-15 08:01:00'--or really any past DATE--that was written before but committed after my previous run.
(I'm not having trouble storing or retrieving this "latest_changed_date_from_last_run" value, but there seems to be no way to use it to get all the data I need. I can only get most of it.)
So is there any way to ensure I can get all rows changed since my last run? If there's no way to do it with a SELECT query, is there some other kind of Change-Data-Capture-lite technique I should be considering for this simple scenario?
(More context: This is in trying to use a Kafka Connect JdbcSourceConnector with an ordinary query, but I imagine this concept could be needed in any system that periodically checks for any updates/inserts in any table.)