Tutorial: Cleaning Up Materialized View Support at a Master Site

If you dropped a materialized view while the materialized view site was not connected to the master site over the network, then you should clean up materialized view support at the master site. Cleaning up materialized view support includes the following actions:

• Unregistering the materialized view

  Unregistering the materialized view removes information about the materialized view in the data dictionary at the master site.

• Either purging the materialized view log or dropping the materialized view log

  If the master table for the materialized view is the master table for other materialized views, then you should purge the materialized view log of information for the materialized view that was dropped. If the master table for the materialized view is not a master table for any other materialized views, then you can drop the materialized view log.

Oracle Database automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log size does not increase endlessly. Because multiple materialized views can use the same materialized view log, rows already used to refresh one materialized view might still be needed to refresh another materialized view. Oracle Database does not delete rows from the log until all materialized views have used them. If you drop a materialized view without cleaning up the master site, then the materialized view log for the materialized view can become very large.

The example in this topic cleans up the following materialized view support:

• The master site is ii1.example.com.

• The materialized view site is ii2.example.com.

• The name of the materialized view is employees_mvr.

• The owner of the materialized view is hr.

To clean up support for this materialized view at the master site: 

1. On a command line, open SQL*Plus and connect to the master site ii1.example.com as an administrative user, such as the replication administrator or SYSTEM. By default, the user name of the replication administrator is repadmin.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Either purge or drop the materialized view log for the master table of the materialized view:

   • If the master table is also the master table for other materialized views in addition to the one that was dropped, then purge the materialized view log of information for the materialized view that was dropped:

     BEGIN
        DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
           mviewowner => 'hr',
           mviewname  => 'employees_mvr', 
           mviewsite  => 'ii2.example.com');
     END;
     /
     

   • If the master table is not a master table for any materialized views other than the one that was dropped, then drop the materialized view log on the master table:

     DROP MATERIALIZED VIEW LOG ON hr.employees;
     

3. Unregister the materialized view:

   BEGIN
      DBMS_MVIEW.UNREGISTER_MVIEW (
         mviewowner => 'hr',
         mviewname  => 'employees_mvr',
         mviewsite  => 'ii2.example.com');
   END;
   /
   

If you are not sure about the materialized view owner, name, or materialized view site, then you can query the ALL_REGISTERED_MVIEWS data dictionary view to obtain this information.