ETL : Tracking changes to data using Materialized View log

Question

I am into designing ETL with source and target database as oracle Standard Edition. For ETL purpose I need to get the changed data everytime.Client does not want any changes to be made in source objects. Is it feasible to create Materialized view log on source database using dblink to track Inser/Update/Delete on the identified tables. Thanks and Regards

Answer 1

I do not believe so -- a materialized view log must be created in the same database as the source object. If the database link were unavailable, your materialized view log would then be incomplete or inaccurate, or worse yet, would be blocking DML against the source table.

I'd recommend instead either:

• Accepting the overhead of a FULL vs FAST refreshable materialized view; or

• Implementing Streams-based replication to have your own copy of the table(s) in question, against which you then implement materialized view logs.