We have an oracle database back end for our SAP ECC system. In it we have a table with over a billion records. We would like extract this table to our SAP BW, which happens to be on MSSQL Server. The table we are trying to extract has no change pointer or column for which to track the changes made against it. Given its volume we are unable to kill and fill it every day. We have looked at Oracle GoldenGate but do not want to spend that kind of money. Has anyone else done this kind of work before?
2 Answers
This sounds like a situation where Oracle Change Data Capture would be appropriate. You would need to write some code that would subscribe to changes to this table and process them by pushing the changes to your SAP BW system.
You can also use Oracle Streams to do heterogeneous replication from Oracle to a non-Oracle database using an Oracle Transparent Gateway.
answered Feb 4, 2013 at 16:06
One option would be to create an oracle trigger which captures the primary key plus the action (UPDATE/INSERT/DELETE) to a custom table and then run some extract in the language of your choice joining the custom table to the real one to get the changed rows by the primary key. Then truncate the custom table so the next run only includes changes since the last run.
