Materialized views are a great way of keeping the same data at two different locations (databases).
At the "source" database you have to create a materialized view log on the desired table. You do this with the following command:
create materialized view log on TEST_TABLE with sequence, primary key including new values;
The above command is used when the table has primary key.When you execute the above command two system tables are generated:
- mlog$_test_table - log table for the changes made to the source table
- rupd$_test_table - log table for controlling the correct order of changes, when a table has primary key
The "destination" database has to have select privileges on the source table (test_table) and the log table (mlog$_test_table).
At the "destination" database execute the following command:
create materialized view TEST_TABLE refresh fast on demand enable query rewrite as select * from TEST_TABLE@SOURCE_DB_LINK ;
You have different options to control writing the changes to the destination, either the refresh fast or refress complete. More on this can be found here.To manualy copy the changes from the source to the destination database, execute the following command on the destination database:
begin
dbms_mview.refresh('TEST_TABLE');
end;
