You Asked

I need to propagate changes on a few tables to an external system. I have to do it near realtime and ultimately it will end up on Kafka from my end. The solution target is Oracle 19c and needs to be installed to multiple clients on premises.

Example problem:
Let's say I have table of messages. I have a column called change_id which is populated by trigger from a sequence on insert and update. Theoretically I always know which row was changed last. i might avoid needing to track deletes, not sure yet.

Now I create a procedure to be ran as a batch and collect rows changed from last run. First it gets the max(change_id), then selects all rows between last_run_max_change_id and new_max_change_id. Exports data as json to another table. writes new_max_change_id as last_run_max_change_id. Commits. Another process will handle delivery of json to where it needs to be.

The problem is another long running transaction might have consumed sequence with lower numbers but has not commited when batch was run, thus those change_ids will never be exported. Another problem is I don't have deleted rows.

Solution 1: Golden Gate replication or OpenLogReplicator or something similar. I would have to convince all clients to commit to paying the GG licence, create tables as replication target, export from those tables, delete from them. Licence and getting all clients on board is difficult, because i need one solution for all. Also security, stability and maintenance concerns will likely make clients want to reject such ideas, and it has to be all of them onboard. I have also tried to use Oracle Streams before on another project and had stability issues and ORA-600 errors that were never resolved.

Solution 2: use SCN instead of change_id. SCNs (ora_scnrow) are not indexed and select by scn in where clause on billions of rows is too slow.

Solution 3: Flashback. Have something like SELECT * FROM messages VERSIONS BETWEEN SCN :last_scn AND :current_scn; My concern is if the program doesn't run for a while for whatever problem and reason, the flashback will be lost. I would need a backup solution.

Solution 4: trigger after insert on messages table that will write to export table. Handles insert, update, delete and nothing will be skipped. I select 10k ids ordered, export to json, delete 10k records and commit; I'm worried about big transactions having additional load to write to export table and trigger overhead for each row. Additional context switching. Index contention on export table having rows at the same time inserted by inserts and updates on original table and rows deleted by export batch process. Exports will have to be ordered by change_id.

Conclusion: The only stable and data consistent solution I can think of is solution 4, a trigger. But I'm worried about overhead.

Instead of a trigger, I could check the code and program additional inserts into export log table so that the total number uf updates might be lower but not by much.

Any other ideas or advice? Is there some auditing feature or clever strategy i didn't think of?
Thanks

edit:
Solution 5: Instead of change-id sequence column written on trigger, I could write a change-flag column that would trigger only on relevant columns. Then I would select for update rows that have change-flag not null, propagate those records to external system and update change-flag to null.
Alternatively, I could update the existing change-id column to null when processed which would drastically reduce index size on change-id also.
With this I would avoid additional tables to be created and overhead of multiple updates generating rows in log table as it is only important to send the latest value as soon as possible.

and Chris said...

Here's some thoughts:

Solution 1: Replicating data is exactly what GoldenGate is for; many customers use this product. If licensing this really will be a blocking issue, you could look at LogMiner to read changes from redo & archive logs. There are products that can help you do this; I've not used any so can't make any recommendations.

Solution 2: By default, ora_rowscn is at the block level, not row. To make it row level you need to define ROWDEPENDENCIES when you create the table. Plus you can't use it to capture deletes, should you need to in the future. This likely rules this out for existing systems.

Solution 3: Flashback Time Travel (previously data archive) enables you to keep a permanent store of changes up to the retention period. This avoids the problem of losing history.

Solution 4: As you say, triggers have overhead. The best way to see if this is viable is to benchmark on your system to see whether this overhead is acceptable. Note that triggers can be disabled, so changes can be lost using this method.

Solution 5: If you've ruled out GG or LogMiner, this is my favourite. You can make the column DEFAULT ON NULL, meaning you only need triggers to set the flag again for updates and capture deletes. You may still need additional tables to capture old values for updates; you'll definitely need extra tables to store delete details.

A column like this also gives you a simple way to re-run the process if the changes are lost downstream for some reason. You just set the flag again.

Another option to consider: Oracle Advanced Queues. This gives you a way to propagate changes to other systems. Release 21c added Transactional Event Queues which can work with Kafka clients https://docs.oracle.com/en/database/oracle/oracle-database/21/adque/interoperability_TEQ_AQ.html You'll need to write to the queue as part of your transactions. You could do this in the app code or with triggers making it similar to option 4.

Rating

(2 ratings)

Comments

A reader, March 11, 2026 - 5:38 pm UTC

Yes, I know about TEQ queues in 21c or 23ai but don't know if I can demand the upgrade sooner than clients generally adopt newer version. Plus, it requires whole application rtesting which is additional cost to everyone.

You pretty much confirmed exactly what i thought about this problem. Thank you. No5 occured to me as soon as I wrote and sent the question. Shows how writing things down or explaining the problem to someone makes you think of solutions. So-called rubber ducky phenomenom :-)

As this is not meant to be permanent solution even a few skipped rows in repiication might be acceptable, but I'm always trying to do things right. Also, we all know there is no more permenant solution than a temporary one.
Thanks again!

March 12, 2026 - 11:11 am UTC

You're welcome.

even a few skipped rows in repiication might be acceptable

I would aim for 100% replication. Occasionally losing changes can be tricky to debug and can cause customers to lose faith in your system.

RW Consistency in Oracle

Rajeshwaran Jeyabal, March 12, 2026 - 12:15 pm UTC

.....
The problem is another long running transaction might have consumed sequence with lower numbers but has not commited when batch was run, thus those change_ids will never be exported
.....

in Session#1 - we do this

demo@ORA26AI> create table oltp
  2  as
  3  select all_users.*, sysdate last_updated
  4    from all_users
  5   where rownum <= 5;

Table created.

demo@ORA26AI>
demo@ORA26AI> create or replace trigger last_updated_trigger
  2  before insert or update on oltp for each row
  3  begin
  4     :new.last_updated := sysdate;
  5  end;
  6  /

Trigger created.

demo@ORA26AI> exec dbms_session.sleep(5);

PL/SQL procedure successfully completed.

demo@ORA26AI> update oltp set username = 'ABC' where rownum = 1;

1 row updated.

demo@ORA26AI>

in session#2 - we do this

demo@ORA26AI> column FIRST_REFRESH new_val FIRST_REFRESH
demo@ORA26AI> select sysdate FIRST_refresh from dual;

FIRST_REFRESH
------------------
12-mar-26 12:01:35

demo@ORA26AI> create table copy as select * from oltp;

Table created.

demo@ORA26AI> column NEXT_REFRESH new_val NEXT_REFRESH
demo@ORA26AI> select sysdate NEXT_refresh from dual;

NEXT_REFRESH
------------------
12-mar-26 12:01:49

demo@ORA26AI> merge into copy
  2  using
  3  (select * from oltp
  4    where last_updated >= to_date('&FIRST_REFRESH') ) OLTP
  5  on ( copy.user_id = oltp.user_id )
  6  when matched then
  7       update set username = oltp.username, created = oltp.created,
  8              last_updated = oltp.last_updated
  9  when not matched then
 10       insert (username,user_id,created,last_updated) values
 11       (oltp.username, oltp.user_id, oltp.created, oltp.last_updated );
old   4:   where last_updated >= to_date('&FIRST_REFRESH') ) OLTP
new   4:   where last_updated >= to_date('12-mar-26 12:01:35') ) OLTP

0 rows merged.

demo@ORA26AI> select 'OLTP', oltp.* from oltp minus select 'OLTP', copy.* from copy
  2  union all
  3  select 'COPY', copy.* from copy minus select 'COPY', oltp.* from oltp;

no rows selected

demo@ORA26AI> /*  these tables are the same now, right -- nothing could be 'wrong'
demo@ORA26AI> but wait.... goto that other session and type 'commit'  */
demo@ORA26AI>

once we do commit in session #1

demo@ORA26AI>
demo@ORA26AI> commit;

Commit complete.

demo@ORA26AI>

and come back to session #2 - we see this

demo@ORA26AI> select 'OLTP', oltp.* from oltp minus select 'OLTP', copy.* from copy
  2  union all
  3  select 'COPY', copy.* from copy minus select 'COPY', oltp.* from oltp;

'OLT USERNAME    USER_ID CREATED            COM O INH DEFAULT_COLLATI IMP ALL EXT DIC CLO APP LAST_UPDATED
---- -------- ---------- ------------------ --- - --- --------------- --- --- --- --- --- --- ------------------
OLTP ABC               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:01:09
COPY SYS               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:00:48

demo@ORA26AI> select to_date('&FIRST_REFRESH') first_refresh,
  2         to_date('&NEXT_REFRESH') next_refresh
  3    from dual;
old   1: select to_date('&FIRST_REFRESH') first_refresh,
new   1: select to_date('12-mar-26 12:01:35') first_refresh,
old   2:        to_date('&NEXT_REFRESH') next_refresh
new   2:        to_date('12-mar-26 12:01:49') next_refresh

FIRST_REFRESH      NEXT_REFRESH
------------------ ------------------
12-mar-26 12:01:35 12-mar-26 12:01:49

demo@ORA26AI> merge into copy
  2  using
  3  (select * from oltp
  4    where last_updated >= to_date('&NEXT_REFRESH') ) OLTP
  5  on ( copy.user_id = oltp.user_id )
  6  when matched then
  7       update set username = oltp.username, created = oltp.created,
  8              last_updated = oltp.last_updated
  9  when not matched then
 10       insert (username,user_id,created,last_updated) values
 11       (oltp.username, oltp.user_id, oltp.created, oltp.last_updated );
old   4:   where last_updated >= to_date('&NEXT_REFRESH') ) OLTP
new   4:   where last_updated >= to_date('12-mar-26 12:01:49') ) OLTP

0 rows merged.

demo@ORA26AI> select 'OLTP', oltp.* from oltp minus select 'OLTP', copy.* from copy
  2  union all
  3  select 'COPY', copy.* from copy minus select 'COPY', oltp.* from oltp;

'OLT USERNAME    USER_ID CREATED            COM O INH DEFAULT_COLLATI IMP ALL EXT DIC CLO APP LAST_UPDATED
---- -------- ---------- ------------------ --- - --- --------------- --- --- --- --- --- --- ------------------
OLTP ABC               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:01:09
COPY SYS               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:00:48

demo@ORA26AI> select to_date('&FIRST_REFRESH') first_refresh,
  2         to_date('&NEXT_REFRESH') next_refresh
  3    from dual;
old   1: select to_date('&FIRST_REFRESH') first_refresh,
new   1: select to_date('12-mar-26 12:01:35') first_refresh,
old   2:        to_date('&NEXT_REFRESH') next_refresh
new   2:        to_date('12-mar-26 12:01:49') next_refresh

FIRST_REFRESH      NEXT_REFRESH
------------------ ------------------
12-mar-26 12:01:35 12-mar-26 12:01:49

to avoid these kind of things to happen, we need to look for open transaction during the first refresh, if exists, then we need to pick that time instead of sysdate.

having this below sql for the "NEXT_refresh" in the above sequence would have solved the problem.

column NEXT_REFRESH new_val NEXT_REFRESH
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate) as NEXT_refresh
from v$transaction;

running the above demo from session #2

demo@ORA26AI> column FIRST_REFRESH new_val FIRST_REFRESH
demo@ORA26AI> select sysdate FIRST_refresh from dual;

FIRST_REFRESH
------------------
12-mar-26 12:09:30

demo@ORA26AI> create table copy as select * from oltp;

Table created.

demo@ORA26AI> column NEXT_REFRESH new_val NEXT_REFRESH
demo@ORA26AI> select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate) as NEXT_refresh
  2  from v$transaction;

NEXT_REFRESH
------------------
12-mar-26 12:09:25

demo@ORA26AI> merge into copy
  2  using
  3  (select * from oltp
  4    where last_updated >= to_date('&FIRST_REFRESH') ) OLTP
  5  on ( copy.user_id = oltp.user_id )
  6  when matched then
  7       update set username = oltp.username, created = oltp.created,
  8              last_updated = oltp.last_updated
  9  when not matched then
 10       insert (username,user_id,created,last_updated) values
 11       (oltp.username, oltp.user_id, oltp.created, oltp.last_updated );
old   4:   where last_updated >= to_date('&FIRST_REFRESH') ) OLTP
new   4:   where last_updated >= to_date('12-mar-26 12:09:30') ) OLTP

0 rows merged.

demo@ORA26AI> /* these tables are the same now, right -- nothing could be 'wrong'
demo@ORA26AI> but wait.... goto that other session and type 'commit' */
demo@ORA26AI> 
demo@ORA26AI> 
demo@ORA26AI> 
demo@ORA26AI> 
demo@ORA26AI> select 'OLTP', oltp.* from oltp minus select 'OLTP', copy.* from copy
  2  union all
  3  select 'COPY', copy.* from copy minus select 'COPY', oltp.* from oltp;

'OLT USERNAME    USER_ID CREATED            COM O INH DEFAULT_COLLATI IMP ALL EXT DIC CLO APP LAST_UPDATED
---- -------- ---------- ------------------ --- - --- --------------- --- --- --- --- --- --- ------------------
OLTP ABC               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:09:26
COPY SYS               0 08-oct-25 22:00:58 YES Y YES USING_NLS_COMP  NO  NO  NO  NO  YES NO  12-mar-26 12:08:46

demo@ORA26AI> select to_date('&FIRST_REFRESH') first_refresh,
  2         to_date('&NEXT_REFRESH') next_refresh
  3    from dual;
old   1: select to_date('&FIRST_REFRESH') first_refresh,
new   1: select to_date('12-mar-26 12:09:30') first_refresh,
old   2:        to_date('&NEXT_REFRESH') next_refresh
new   2:        to_date('12-mar-26 12:09:25') next_refresh

FIRST_REFRESH      NEXT_REFRESH
------------------ ------------------
12-mar-26 12:09:30 12-mar-26 12:09:25

demo@ORA26AI> merge into copy
  2  using
  3  (select * from oltp
  4    where last_updated >= to_date('&NEXT_REFRESH') ) OLTP
  5  on ( copy.user_id = oltp.user_id )
  6  when matched then
  7       update set username = oltp.username, created = oltp.created,
  8              last_updated = oltp.last_updated
  9  when not matched then
 10       insert (username,user_id,created,last_updated) values
 11       (oltp.username, oltp.user_id, oltp.created, oltp.last_updated );
old   4:   where last_updated >= to_date('&NEXT_REFRESH') ) OLTP
new   4:   where last_updated >= to_date('12-mar-26 12:09:25') ) OLTP

1 row merged.

demo@ORA26AI> select 'OLTP', oltp.* from oltp minus select 'OLTP', copy.* from copy
  2  union all
  3  select 'COPY', copy.* from copy minus select 'COPY', oltp.* from oltp;

no rows selected

demo@ORA26AI>

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

RW Consistency in Oracle

More to Explore

PL/SQL demos

PL/SQL docs

Continuous data capture

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

RW Consistency in Oracle

More to Explore

PL/SQL demos

PL/SQL docs