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