Since you are on 10g, you could potentially use the ORA_ROWSCN
pseudocolumn. That gives you an upper bound of the last SCN (system change number) that caused a change in the row. Since this is an increasing sequence, you could store off the maximum ORA_ROWSCN
that you've seen and then look only for data with an SCN greater than that.
By default, ORA_ROWSCN
is actually maintained at the block level, so a change to any row in a block will change the ORA_ROWSCN
for all rows in the block. This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we're talking about "normal" data access patterns. You can rebuild the table with ROWDEPENDENCIES
which will cause the ORA_ROWSCN
to be tracked at the row level, which gives you more granular information but requires a one-time effort to rebuild the table.
Another option would be to configure something like Change Data Capture (CDC) and to make your OCI application a subscriber to changes to the table, but that also requires a one-time effort to configure CDC.