How can I get all data from a given table since my last run? 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 &gt; :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 &quot;latest_changed_date_from_last_run&quot; 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.)
I think you can base your query on the ORA_ROWSCN pseudocolumn.

If two transactions T1 and T2 modified the same row R, one after
another, and committed, a query on the ORA_ROWSCN of row R after the
commit of T1 will return a value lower than the value returned after
the commit of T2

For this use case, the table must be created with ROWDEPENDENCIES (cannot be changed after table creation)

With this feature, each row in the table has a
system change number (SCN) that represents a time greater than or
equal to the commit time of the last transaction that modified the
row.

If you can recreate the table with ROWDEPENDENCIES, then you would swap your latest_changed_date_from_last_run for latest_ora_rowscn_from_last_run and that should do the job.