Materialized views in Dbvisit Replicate

Owned by Clive Otten (Unlicensed)
Jan 22, 2020
1 min read

Problem Description

This article answers few questions based on replicating materialized views

Questions

  • I've got a few materialized views, do i need to create them on both of the databases?

  • Ok, I've created a materialized view on one of my servers, and replicate has created a table on the other database?

Answer

The easiest way is to not replicate the mviews at all. Make sure they are not prepared on source. On the target, it's up to you if you actually need the mviews there. If you create a mview on the target, Oracle will maintain it for you, without Replicate doing anything to the mview (just replicating data for the mview base table(s)).

If you prepare a mview, Replicate actually replicates the table that stores the data. What is created on the target database depends on the way it was created (Oracle export/data pump will tend to create an mview, Replicate will tend to create a plain table).

Question

If I'm adding a new mview, how do stop replicate from preparing it? At the moment my ddc is setup to prepare the whole schema?

Answer

If this is when you prepare the whole schema (like when *-all.sh is run), simply run UNPREPARE TABLE after the PREPARE SCHEMA. If this is when replication is already running and you want to stop auto-prepare of a newly created mview/table, use EXCLUDE CREATE TABLE (see HELP EXCLUDE for exact syntax). With this, you can specify a table name/wildcard that are not auto-prepared.

Arjen Visser May 29, 2013 10:10