How can I get all data from a given table since my last run?

Question

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.)

Answer 1

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.

Answer 2

You could set up a personal table, one DATE column, inserting one row after each time you query the target table. Since your table column you are interested in is in DATE format, keep your personal table column the same data type; using TIMESTAMP will not help.

Example personal table, named last_look and target table named sales:

alter session set nls_date_format = 'YYYY-mm-dd hh24:mi:ss';
create table last_look (looked_last date default on null sysdate not null) tablespace users;

And now load some data and test. Sorry for the length of this, but I wanted to check my answer!

22:30:43 MARK.STEWART> insert into last_look values(null);
1 row created.

22:31:05 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58

22:31:14 MARK.STEWART> create table sales (cust_id number, changed_date date default sysdate) tablespace users;

Table created.

22:31:52 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

no rows selected

22:32:31 MARK.STEWART> insert into last_look values(null);
1 row created.

22:32:41 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58
2021-01-15 22:32:41

22:32:44 MARK.STEWART> insert into sales (cust_id) values (33);
1 row created.

22:32:57 MARK.STEWART>  insert into sales (cust_id) values (34);
1 row created.

22:33:01 MARK.STEWART> commit;
Commit complete.

22:33:04 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:15 MARK.STEWART> select * from sales;

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:22 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:31 MARK.STEWART> insert into sales (cust_id) values (38);
1 row created.

22:33:39 MARK.STEWART> commit;
Commit complete.

22:33:41 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01
        38 2021-01-15 22:33:39

22:33:49 MARK.STEWART> insert into last_look values(null);
1 row created.

22:33:57 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58
2021-01-15 22:32:41
2021-01-15 22:33:57

22:34:03 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);
no rows selected

22:34:07 MARK.STEWART> insert into sales (cust_id) values (39);
1 row created.

22:34:33 MARK.STEWART> commit;
Commit complete.

22:34:35 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        39 2021-01-15 22:34:33

22:34:40 MARK.STEWART> insert into last_look values(null);
1 row created.

22:34:46 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);
no rows selected

22:34:50 MARK.STEWART> select * from sales;

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01
        38 2021-01-15 22:33:39
        39 2021-01-15 22:34:33

Answer 3

im thinking that for your case you should have two columns , one for the timestamp just before the update and one for the time right after the update has been commited. that way you could build two queries that uses the data from both these columns to get an accurate picture of data insertion times and be able to answer your questions for example, "have there been an row that was changed before the last commit i see?" in that example you will be able to query the last time an update was fired but still had not commited and answer your own questions like that

sources that could be relevant for these:

https://mkyong.com/oracle/oracle-plsql-before-update-trigge-example/

http://www.dba-oracle.com/t_dml_auditing.htm