Question: I am having trouble creating a
distributed materialized view using a database link.
Do you have any tips on using a database link to join a local
table and a remote table using a materialized view?
Does the
name of the database link have to be in the tnsnames.ora
file for the database link to work with the materialized views for
capturing data at a point in time on the remote instance?
Answer: You
can create a local materialized view against a remote table and join
a local table and a remote table using a materialized view.
The database link is used to accomplish this, and as a result, the
materialized view will refresh properly. It is one of the
simplest ways to achieve replication between a local and remote
site.
The steps for creating replication through the use of
materialized views with Oracle are fairly simple:
- Verify the privileges of the user who will own the
materialized views
- Connect to the user who will own the materialized views and
create the views along with the database link keeping the
following in mind:
- Using the FORCE parameter will allow an initial attempt
at a fast refresh. If the fast refresh fails, a
complete refresh will happen.
- To set the refresh rate, create a refresh group and
assign the new materialized view to it. As one part of
the setup, the following can be used to set the refresh
interval to 1 minute: interval =>
'/*1:Mins*/ sysdate + 1/(60*24)',
- On the master instance, set up the materialized view log,
which will allow the fast refresh to work.
