Create Materialised View Log on the Remote Database

DB1 is a local database, on which the materialized views are to be created.
DB2 is a remote database, on which materialized view logs are created .

The high level steps are:

  • on local database DB1, create a database link between DB1 and DB2 database.
  • On the remote database DB2, create materialized logs on the tables of DB2.
  • On local database DB1, create materialized views with Fast Refresh option , etc.
  • On Local database DB1, run a complete refresh on your materialized views:          Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’c’);
  • Then on local database DB1 again ,  refresh the materialized  view using the Fast Refresh option:
    Exec dbms_snapshot.refresh(‘schema_name.mview_name’,’f’);