The following sections describe Replication Agent behavior with respect to Oracle materialized views.
By default, Replication Agent does not replicate Oracle DDL commands used for materialized views, for example, the CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, or DROP MATERIALIZED VIEW commands. Materialized view DDL commands are disabled from replication unless otherwise specified using the pbd_setrepddl command. To enable materialized view DDL commands for replication, see the pdb_setrepddl command in the Replication Agent Reference Manual.
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 will replicate this master table. The materialized view at the replicate database will refresh according to the contents of the replicated master table. Under no circumstances will 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 will fail at the replicate database. In redirecting the replicate database, instead of recreating the materialized view, you should recreate the Oracle database link that the replicate database uses to connect to the database containing the master table.
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 will replicate 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 will only affect the local master table unless bidirectional replication has been enabled.
In Figure 1-1, a materialized view and a corresponding master table reside on both the primary database and the replicate database.
Figure 1-1: Master table and materialized view on primary 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 will not be 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 will not be changed accordingly unless bidirectional replication has been enabled.
In Figure 1-2, the master table on which the primary database materialized view is defined resides on a different, or remote, database.
Figure 1-2: Master table on remote database, materialized view on primary database
In this situation, neither DML nor DDL affecting the master table will be replicated. DDL commands affecting the materialized view will not be 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 will be reflected in both materialized views upon refresh.
In Figure 1-3, 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.
Figure 1-3: Master table on primary database, materialized view on remote database
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 Figure 1-4, 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 recreates a link to the remote replicate database instead.
Figure 1-4: Master tables on remote databases, materialized views on primary and replicate databases
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.
