2

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

7
  • 2
    And how do you plan on keeping track of "the last time you checked?" Maybe a small one row, one date column table which you update after each time you look? And sounds like the database is doing what it is supposed to; consistent reads, how did the committed data look at the exact time your query began. Jan 15 '21 at 21:02
  • Yes, I can keep track of it in another table, or in memory, or on disk in a text file, etc. I agree, it is doing what it is supposed to. Any suggestions on how to get all data from a given table since my last run? Jan 15 '21 at 21:09
  • Any suggestions on how to get all rows that changed that I didn't get in my last run? (It would even be acceptable to get some small amount additional "noise" rows that didn't change, too, as long as I surely get all the rows that changed.) Jan 15 '21 at 21:15
  • 1
    "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 value". That is simply not possible. Until a transaction is committed, the changes it makes are not visible by anyone. That is guaranteed by the lowest level of consistency (read committed) that Oracle enforces. Reading •uncommitted data* is just not possible. Jan 16 '21 at 16:59
  • 1
    I don't want to read uncommitted data on the first run, I want to, on the second run, read the data that was committed just after the first run. However, my WHERE clause would exclude it from the second run, as explained above. So I see no way to read such data at all by such a job. Jan 16 '21 at 18:04
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.

1
  • 1
    ROWDEPENDENCIES aren't needed if the subscriber is OK with receiving extra rows that haven't been modified -- which is relatively easy to deal with (eg using MERGE), and IMHO preferable to missing rows. In that case ORA_ROWSCN is stored at the block level so you will get all records in the same block if one record has been modified (inserted, updated, or deleted). But be aware that using ORA_ROWSCN won't tell you which rows have been deleted on the source. Jan 26 '21 at 8:50
-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
6
  • I think I wasn't perfectly clear--let me elaborate: I'm having trouble in querying the table, not on storing the last look date. Just querying sales on changed_date will not reliably give me everything that changed, since a row could be legitimately written to sales with a changed_date of a few seconds (or even longer) before it was committed. Jan 15 '21 at 21:46
  • 1
    And by the way, TIMESTAMP '2021-01-15 08:01:02' is just the Oracle way of supplying a date+time literal; it is a DATE type, not a TIMESTAMP type. Jan 15 '21 at 21:50
  • As has been explained already, Oracle guarantees statement level consistency. Meaning that the data seen by a query is the state as it is in when the query started. If updates are done concurrently (and committed) while your query runs, they will not be seen by that query. They will be seen when you repeat your query. Jan 16 '21 at 16:55
  • 2
    Your answer won't work, Mark. Time 1: producer inserts rows with timestamp 1. Time 2: consumer reads rows since timestamp 0 -- doesn't see rows from producer yet as they haven't been committed. Consumer updates last read timestamp to 2. Time 3: producer commits transaction, making rows with timestamp 1 visible to other users. Time 4: consumer runs again and tries to find rows after timestamp 2 which obviously doesn't include the rows with timestamp 1 that are now visible. Jan 26 '21 at 8:45
  • Thanks @Colin'tHart; good example. I knew I should've made a little time-line spreadsheet! Jan 26 '21 at 18:30
-2

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

5
  • 1
    that is no an answer. The OP has a precise question, you neither tell us how to record these timestamps nor how to query the data
    – miracle173
    Jan 24 '21 at 17:20
  • i gave him a solution that works , i just didnt specify the exact details, my answer could help him the most out of all the other answers here, so how is it not a good answer? i also gave him exact links to the websites explaining how to execute my solution to his problem. what did you expect that i would generate a pseudo commands for him the fill in the blanks and execute? no. that is never good. you should give 99% of the solution , if the asker cannot complete that 1% , imho he does not deserve a correct answer.
    – Asaf
    Jan 25 '21 at 7:31
  • op, if you have follow up questions on how to implement my solution , feel free to ask
    – Asaf
    Jan 25 '21 at 7:35
  • 1
    You have some kind producer-consumer problem, and you should describe precisely who writes and reads what values and takes which actions to achieve the desired goal
    – miracle173
    Jan 25 '21 at 21:24
  • @Asaf Oracle doesn't have an "on commit timestamp" for rows. There's no way to do this! Jan 26 '21 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.