Skip to Main Content
  • Questions
  • Filter Incremental Data in Oracle DB

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Himanshu.

Asked: June 03, 2019 - 10:21 am UTC

Last updated: June 05, 2019 - 6:25 am UTC

Version: 4.1.5.21

Viewed 1000+ times

You Asked

Hi TOM, I have requirement to filter differential/Incremental data in Oracle Database. Primary key that I use is composite key (combination of 2 columns). They are in such a shape that I am unable to prepare 'where' condition that can help me to identify records (> ie ...greater than a primary key value). I have to filter these records and transfer to MSSQL DB. I tried with 'rowid' feature of Oracle DB (rowid value=equivalent to a record for my breakpoint). QUERY: select count(rowid) from TABLE_NAME where rowid > 'AAAoS5AGMAAFtq2AAM'-- This query returns me records far beyond my expected count. Is there any way in ORACLE DB to identify incremental data in this situation? Please help.
I am using SSMA - ORACLE for data transfer.

and Connor said...

Goldengate would seem a good choice here to take care of all of this.

But if you *really* want a home grown solution, you could look at triggers to keep a record of rows

SQL> create table t ( x int primary key, y int, z int);

Table created.

SQL>
SQL> create table tracker ( seq int, x int , action varchar2(1));

Table created.

SQL>
SQL> create sequence seq;

Sequence created.

SQL>
SQL>
SQL> create or replace
  2  trigger trg
  3  after insert or update or delete
  4  on t
  5  for each row
  6  begin
  7    if deleting or updating then
  8      insert into tracker values (seq.nextval,:old.x, 'D');
  9    end if;
 10
 11    if inserting or updating then
 12      insert into tracker values (seq.nextval,:new.x, 'I');
 13    end if;
 14  end;
 15  /

Trigger created.

SQL>
SQL> insert into t values (10,10,10);

1 row created.

SQL> insert into t values (12,12,12);

1 row created.

SQL> update t set x = 13 where y = 12;

1 row updated.

SQL> delete from t;

2 rows deleted.

SQL> select * from tracker order by seq;

       SEQ          X A
---------- ---------- -
         1         10 I
         2         12 I
         3         12 D
         4         13 I
         5         10 D
         6         13 D

6 rows selected.


I remap updates to delete-insert to handle primary key changes. You then need to replay those changes in sequence order to your target.

But I stress - this is not as trivial as it looks. There is locking to take care of, plus the tradeoff between logging all of the columns versus deciding which updates to apply.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database