Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Create a Materialized View Using a Database Link

Expert Oracle Database Tips by Donald BurlesonNovember 30, 2015

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.

Failure of the fast refresh can result in an ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view error.  More information on the ON COMMIT and ORA-12054 are available HERE.

It has been suggested that documentation on the newest version of the Oracle database indicates that the database link name must be the same as the global name of the target database; however, I have not seen the documentation to prove that.  It is true that the database link name does not have to be present in the tnsnames.ora file as the database link itself specifies the TNS. 

Jim Czuprynski notes the following with regard to accessing remote tables via a database link:

In 10g and beyond, an MV with table joins is now permitted to contain references to remote tables, which are tables that are accessed via a public or private database link.

If the FROM clause of the MJV does reference any remote tables, then all the tables in the FROM clause must be located at the same site.

Also, note that the ROWID rules mentioned above also hold true if the MJV is to be fast-refreshed.

The link can be either public or private.  Access to private links can be granted to anyone who will invoke them.

There are performance issues with database links that must be considered when deciding on taking this approach.

Materialized views through database links are not just for Oracle databases.  You can create materialized views on external database products like DB2 and SQL Server (MSSQL) using Goldengate and  Oracle heterogeneous services.

The Oracle Replication:Expert Methods for Robust Data Sharing book has complete details and full step-by-step instructions for creating a materialized view over a database link.  A taste of the excellent information supplied in this book is available HERE.

A reader having an issue with materialized views using database links writes:

The weird thing was that the query worked perfectly, it just froze when trying to create the snapshot.  After analyzing the issue, the cause of the problem was the LOGGING clause in the materialized view script. I changed it to NOLOGGING and no more problems since then.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster