Correcting Problems with Materialized View Refresh

If one or more materialized views at a materialized view site cannot refresh, then the following are typical causes and solutions:

• A network problem is preventing one or more materialized views from refreshing. If you have multiple refresh groups and multiple materialized views at a materialized view site, and none of them can refresh, then a network problem is the most likely cause. In this case, correct the network problem, and refresh the relevant materialized views. See Configuring the Network Environment for information about configuring network connectivity between databases.

• A master site for the materialized views is down. A materialized view cannot refresh if its master site is not open. If you have a materialized view site with materialized views that use multiple master sites, and only the materialized views for a particular master site cannot refresh, then a master site problem is the most likely cause. In this case, check the database at the master site. If it is down, then start it, and refresh the relevant materialized views. See About Instance Startup for information about starting a database.

• A database link is broken. If all of the materialized views in a materialized view group cannot refresh, then the database link used to refresh the materialized views might be broken. If a database link is broken, then you can correct the problem by deleting the database link and re-creating it.

To re-create a broken database link: 

1. Log in to Enterprise Manager as an administrative user, such as SYSTEM.

2. Go to the Database Home page.

3. Click Schema to open the Schema subpage.

4. Click Database Links in the Database Objects section.

   The Database Links page appears.

5. Use the search tool to list the database link used by the materialized views.

   The database link name should be the same as the global name of the master site for the materialized views.

6. Select the database link.

7. Click View.

8. On the View Database Link page, click Test to see if the database link is active. If it is active, then the materialized view refresh problems are caused by a different issue. If the database link is not active, then follow Steps 9 through 13 to delete it and re-create it.

9. Click OK to return to the Database Links page.

10. Select the database link.

11. Click Delete.

12. Click Yes on the confirmation page to delete the database link.

13. Follow the instructions in "Tutorial: Creating a Database Link" to re-create the database link.