An Oracle materialized view allocates space to hold the result set of its base query. Replication Agent can replicate transactions involving the data on which a materialized view is defined as well as on the materialized view itself.
Materialized View DDL
By default, Replication Agent does not replicate Oracle DDL commands used for materialized views, for example, CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, or DROP MATERIALIZED VIEW. Materialized view DDL commands are disabled from replication unless otherwise specified using the pbd_setrepddl command. See the Replication Agent Reference Manual > Command Reference > pdb_setrepddl.
Materialized Views at the Primary and Replicate Databases
A materialized view may exist on both the primary database and the replicate database. Such a situation might arise, for example, if materialized view DDL has been enabled for replication with the pdb_setrepddl command or if the replicate database has been materialized from a primary database dump.
If the master table on which the materialized view is defined exists in the primary database, Replication Agent replicates this master table. The materialized view at the replicate database refreshes according to the contents of the replicated master table. Under no circumstances does Replication Agent replicate the table in which a materialized view is stored in the primary database, and you should not attempt to replicate such a table.
If the materialized view is remote—meaning that the master table on which the materialized view is defined does not exist in the primary database—the materialized view at the replicate database must be redirected so that it points to the database on which the master table is located. If the replicate database is not redirected, a refresh of the materialized view fails at the replicate database. In redirecting the replicate database, re-create the Oracle database link that the replicate database uses to connect to the database containing the master table.
Writeable and Updatable Materialized Views
Instead of replicating changes to the table containing a materialized view, Replication Agent replicates changes to the master table if the master table has been marked for replication. Replication Agent therefore does not replicate changes made to a writeable materialized view. However, because changes made to an updatable materialized view are written back to the corresponding master table or tables when the materialized view is refreshed, Replication Agent replicates changes made to an updatable materialized view on the primary database to the corresponding master table on the replicate database. Changes made to an updatable materialized view on the replicate database affect only the local master table unless bidirectional replication has been enabled.
Materialized View Replication Scenarios
In this figure, a materialized view and a corresponding master table reside on both the primary database and the replicate database.
In this situation, DDL commands affecting the master table can be replicated as well as objects affected by the DML that are marked for replication.
DDL commands affecting the materialized view are not replicated unless such DDL is enabled with the pdb_setrepddl command. Since a materialized view also exists on the replicate database, all master tables on which the materialized view is defined must also be replicated. Otherwise, the contents of the materialized view on the replicate database may become invalid.
If the materialized view on the primary database is updatable, changes made to this view are written back to the corresponding master table and, if the master table has been marked for replication, replicated to the replicate database. If the materialized view on the replicate database is updatable, changes made to this view are written back to the corresponding master table on the replicate database, but the master table on the primary database is not changed accordingly unless bidirectional replication has been enabled.
In this figure, the master table on which the primary database materialized view is defined resides on a different, or remote, database.
In this situation, neither DML nor DDL affecting the master table is replicated. DDL commands affecting the materialized view are not replicated unless such DDL is enabled with the pdb_setrepddl command. Since the materialized view also exists on the replicate database, a database link must be created so that it points to the database containing the master table on which the materialized view is defined.
If the materialized views on the primary and replicate databases are both updatable and are properly linked to the master table at the remote database, changes made to one of these views are written back to the master table, and the changes are reflected in both materialized views upon refresh.
In the figure below, a materialized view resides on a remote database, and the master table on which the materialized view is defined resides on the primary database. A copy of this master table also resides on the replicate database, and the database link between the remote and primary databases is subsequently broken.
In this situation, DDL commands affecting the master table at the primary database can be replicated as well as DML commands that are marked for replication. DDL commands affecting the materialized view cannot be replicated because there is no corresponding materialized view on the replicate database. If the database link between the remote and primary databases is broken, the remote database must create a link to the replicate database before a refresh occurs.
If the materialized view on the remote database is updatable, changes made to this view are written back to the master table on the database to which the remote database is currently linked.
In the figure below, a master table resides on two different remote databases, one of which is a replicate database. A materialized view resides on the primary database and the replicate database. The materialized view at the replicate database is initially defined by the master table on the remote database, but its database link becomes broken, and the replicate database re-creates a link to the remote replicate database instead.
If the materialized view on the replicate database is updatable, changes made to this view are written back to the master table on the database to which the replicate database is currently linked. Before the database link between the replicate database and the remote database becomes broken, updates to the materialized view on the replicate database are written back only to the master table on the remote database. After a link is created between the replicate database and the remote replicate database, updates to the materialized view on the replicate database are written back only to the master table on the remote replicate database.