If you think a database link is not functioning properly, then you can drop and re-create it using Oracle Enterprise Manager Cloud Control, SQL*Plus, or another tool.

CREATE DATABASE LINK dbs1.example.com@myethernet CONNECT TO myadmin
  IDENTIFIED BY password USING 'connect_string_myethernet';

There are items to check if you have problems creating a materialized view.

• Common Refresh Problems
  Several common factors can prevent the automatic refresh of a group of materialized views.
• Automatic Refresh Retries
  When Oracle Database fails to refresh a refresh group automatically, the refresh group remains due for its refresh to complete.
• Fast Refresh Errors at New Materialized View Databases
  In some cases, a materialized view log for a master table might be purged during the creation of a materialized view at a new materialized view database.
• Materialized Views Continually Refreshing
  If you encounter a situation where Oracle Database continually refreshes a group of materialized views, then check the group's refresh interval.
• Materialized View Logs Growing Too Large
  If a materialized view log at a master database is growing too large, then check to see whether a network or database failure has prevented the master database from becoming aware that a materialized view has been dropped.

There are several items you can check if you have problems with refreshing a materialized view.

If you have a problem refreshing a materialized view, then try the following:

• Check the NEXT_DATE value in the DBA_REFRESH_CHILDREN view to determine if the refresh has been scheduled.

• If the refresh interval has passed, then check the DBA_REFRESH view for the associated job number for the materialized view refresh and then diagnose the problem with job queues.

• Check if there are job slaves running. Check the JOB_QUEUE_PROCESSES initialization parameter, query the DBA_JOBS_RUNNING view, and use your operating system to check if the job slaves are still running.

• You also might encounter an error if you attempt to define a master detail relationship between two materialized views. You should define master detail relationships only on the master tables by using declarative referential integrity constraints. The related materialized views should then be placed in the same refresh group to preserve this relationship. However, you can define deferred (or deferrable) constraints on materialized views.

• Materialized views in the same refresh groups have their rows updated in a single transaction. Such a transaction can be very large, requiring either a large rollback segment at the materialized view database, with the rollback segment specified to be used during refresh, or more frequent refreshes to reduce the transaction size.

• If Oracle error ORA-12004 occurs, then the master database might have run out of rollback segments when trying to maintain the materialized view log, or the materialized view log might be out of date. For example, the materialized view log might have been purged or re-created.

• Complete refreshes of a single materialized view internally use the TRUNCATE feature to increase speed and reduce rollback segment requirements. However, until the materialized view refresh is complete, users might temporarily see no data in the materialized view. Refreshes of multiple materialized views (for example, refresh groups) do not use the TRUNCATE feature.

• Reorganization of the master table (for example, to reclaim system resources) should TRUNCATE the master table to force rowid materialized views to do complete refreshes. Otherwise, the materialized views have incorrect references to master table rowids. You use the BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION procedures in the DBMS_MVIEW package to reorganize a master table.

• If while refreshing you see an ORA-00942 (table or view does not exist), then check your database links and ensure that you still have the required privileges on the master table and the materialized view log.

• If a fast refresh was succeeding but then fails, then check whether:

  • The materialized view log was truncated, purged, or dropped.

  • You still have the required privileges on the materialized view log.

• If a force refresh takes an inordinately long time, then check if the materialized view log used by the refresh has been dropped.

• If the materialized view was created with BUILD DEFERRED, and its first fast refresh fails, then ensure that a previous complete refresh was done successfully before checking for other problems.