Oracle Database Discussions

Hello,

I have a curious problem I have been battling with for a while, still haven't been able to figure it out. I hope someone will have an advice for me. :)

So, I am using Oracle Data Integrator 11g to load an Oracle DB. Source is Oracle DB as well, connection is made through a DBLINK.

I have an requirement to fetch data using the AS OF SCN flashback query.

My initial thought was to implement it in the Oracle LKM, when creating the view. But it seems you can't create a view using the AS OF SCN over the DBLINK.

So I implemented it in the Oracle IKM, when loading the I$ table. I've modified it to include the AS OF SCN query.

Now, it works fine, on each rerun I get new data in my target database.

But when I query the source database using the SCN which ODI used to load the target, I get less rows returned.

I found the records which shoudn't exist in the target, and ora_rowscn is greater than the SCN ODI used.

I checked other records from the target, which match with the records from query on the source with the SCN, and I can see that the ora_rowscn for those is smaller than the SCN ODI used.

I haven't been able to figure out why does ODI include those records, when it clearly shouldn't.

My thoughts:

1. Could it be somehow related to "dirty blocks"?

Is it possible that somehow ODI creates view containing "dirty blocks", after which those dirty blocks are cleansed on the source, and get the new SCN?

But when using the flashback query on the view, for the insert into I$, shouldn't it exclude dirty blocks?

2. When creating the view over dblink, does ODI copy the SCN as well, or is the view created without SCN? If that's the case, then the AS OF SCN query on insert into I$ step would make no sense. The view is created for every schema in the source, and then data inserted into

one table in target.

Any advice or a thought is appreciated.
Thanks!