删除物化视图最安全的方法：

1. 确保物化视图站点和主站点连接正常。

2. 在物化视图站点删除物化视图组。

3. 在物化视图站点删除物化视图对象。

当主站点和物化视图站点之间链路不正常时，可能需要通过如下步骤操作之后删除主站点的相关元数据。

4. 可选步骤，在主站点注销未被正常注销的物化视图组,清理未被正常注销的物化视图对象的物化视图日志。

5. 可选步骤，在主站点注销未被正常注销的物化视图对象,清理未被正常注销的物化视图对象的物化视图日志。

例(来自10G联机文档)：

1. 在物化视图站点删除物化视图组：

执行角色： Materialized View Administrator

执行位置： Remote Materialized View Site

操作步骤：

Step 1 Connect to the materialized view site as the materialized view administrator.

CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Drop the materialized view group.

BEGIN

DBMS_REPCAT.DROP_MVIEW_REPGROUP (       gname => 'hr_repg',       drop_contents => TRUE); END; /

If you want to physically remove the contents of the materialized view group from the materialized view database, then be sure that you specify TRUE for the drop_contents parameter.

2. 在物化视图站点删除物化视图对象：

执行角色： Materialized View Administrator

执行位置： Remote Materialized View Site

操作步骤：

Step 1 Connect to the materialized view site as the materialized view administrator.

CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Drop the materialized view.

BEGIN

DBMS_REPCAT.DROP_MVIEW_REPOBJECT (       sname => 'hr',        oname => 'employees_mv1',        type => 'SNAPSHOT',        drop_objects => TRUE); END; /

If you want to physically remove the contents of the materialized view from the materialized view database, then be sure that you specify TRUE for the drop_contents parameter.

3. 在主站点注销未被正常注销的物化视图组,清理未被正常注销的物化视图对象的物化视图日志。：

执行角色： Replication Administrator

执行位置： Master Site or Master Materialized View Site for Target Materialized View Site

操作步骤：

Step 1 Connect to the master site or master materialized view site as the replication administrator.

*/

SET ECHO ON  SPOOL cleanup_master1.out  CONNECT repadmin/repadmin@orc1.world  /*

Step 2 Unregister the materialized view groups.

*/

BEGIN    DBMS_REPCAT.UNREGISTER_MVIEW_REPGROUP (       gname => 'hr_repg',       mviewsite => 'mv1.world'); END; /  /*

Step 3 Purge the materialized view logs of the entries that were marked for the target materialized views.

Execute the PURGE_MVIEW_FROM_LOG procedure for each materialized view that was in the materialized view groups you unregistered in Step 2.

If for some reason unregistering the materialized view group fails, then you should still complete this step.

*/  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'countries_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'departments_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'employees_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'jobs_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'job_history_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'locations_mv1',        mviewsite => 'mv1.world'); END; /  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'regions_mv1',        mviewsite => 'mv1.world'); END; /  SET ECHO OFF  SPOOL OFF  /************************* END OF SCRIPT **********************************/

注意:

在联机文档中使用的是

mviewowner+

mviewname+

mviewsite来对主站点的物化视图日志进行清理，实际上是不合理的，应该使用

mview_id来标识一个物化视图。（因为往往是有很多个物化视图站点的，而站点名可能重复）

4. 在主站点注销未被正常注销的物化视图对象,清理未被正常注销的物化视图对象的物化视图日志。：

执行角色： Replication Administrator

执行位置： Master Site or Master Materialized View Site for Target Materialized View Site

操作步骤：

Step 1 Connect to the master site or master materialized view site as the replication administrator.

*/

SET ECHO ON  SPOOL cleanup_master2.out  CONNECT repadmin/repadmin@orc1.world  /*

Step 2 Unregister the materialized view.

*/

BEGIN    DBMS_MVIEW.UNREGISTER_MVIEW (       mviewowner => 'hr',       mviewname => 'employees_mv1',       mviewsite => 'mv1.world'); END; /  /*

Step 3   Purge the associated materialized view log of the entries that were marked for the target materialized views.

If for some reason unregistering the materialized view fails, then you should still complete this step.

*/  BEGIN    DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (       mviewowner => 'hr',       mviewname => 'employees_mv1',        mviewsite => 'mv1.world'); END; /  SET ECHO OFF  SPOOL OFF  /************************* END OF SCRIPT **********************************/

注意:

在联机文档中使用的是

mviewowner+

mviewname+

mviewsite来对主站点的物化视图日志进行清理，实际上是不合理的，应该使用

mview_id来标识一个物化视图。（因为往往是有很多个物化视图站点的，而站点名可能重复）

相关管理接口介绍:

DROP_MVIEW_REPGROUP Procedure

原文:

This procedure drops a materialized view site from your replication environment. DROP_MVIEW_REPGROUP automatically calls UNREGISTER_MVIEW_REPGROUP at the master site or master materialized view site to unregister the materialized view, but ignores any errors that might have occurred during unregistration. If DROP_MVIEW_REPGROUP is unsuccessful, then connect to the master site or master materialized view site and runUNREGISTER_MVIEW_REPGROUP.

语法:

DBMS_REPCAT.DROP_MVIEW_REPGROUP (    gname                 IN   VARCHAR2,     drop_contents         IN   BOOLEAN   := false,    gowner                IN   VARCHAR2  := 'PUBLIC');

参数介绍:

gname

Name of the replication group that you want to drop from the current materialized view site. All objects generated to support replication, such as triggers and packages, are dropped.

drop_contents

By default, when you drop the replication group at a materialized view site, all of the objects remain in their associated schemas. They simply are no longer replicated. If you set this to true, then any replicated objects in the replication group are dropped from their schemas.

gowner

Owner of the materialized view group.

DROP_MVIEW_REPOBJECT Procedure

原文:

This procedure drops a replicated object from a materialized view site.

语法:

DBMS_REPCAT.DROP_MVIEW_REPOBJECT (     sname          IN   VARCHAR2,     oname          IN   VARCHAR2,     type           IN   VARCHAR2,     drop_objects   IN   BOOLEAN  := false);

参数介绍:

sname	Name of the schema in which the object is located.
oname	Name of the object that you want to drop from the replication group.
type	Type of the object that you want to drop. The following types are supported: FUNCTION SNAPSHOT INDEX SYNONYM INDEXTYPE TRIGGER OPERATOR TYPE PACKAGE TYPE BODY PACKAGE BODY VIEW PROCEDURE Use `SNAPSHOT` to drop a materialized view.
drop_objects	By default, the object remains in its associated schema, but is dropped from its associated replication group. To completely remove the object from its schema at the current materialized view site, set this parameter to `true`. If the parameter is set to `true`, the object is dropped from the database at the materialized view site.

UNREGISTER_MVIEW_REPGROUP Procedure

原文:

This procedure facilitates the administration of materialized views at their respective master sites or master materialized view sites by deleting a materialized view group from DBA_REGISTERED_MVIEW_GROUPS. Run this procedure at the master site or master materialized view site.

语法:

DBMS_REPCAT.UNREGISTER_MVIEW_REPGROUP (    gname      IN   VARCHAR2,    mviewsite  IN   VARCHAR2,    gowner     IN   VARCHAR2  := 'PUBLIC');

参数介绍:

gname

Name of the materialized view group to be unregistered.

mviewsite

Global name of the materialized view site.

gowner

Owner of the materialized view group.

UNREGISTER_MVIEW Procedure

原文:

This procedure enables the administration of individual materialized views. It is invoked at a master site or master materialized view site to unregister a materialized view.

语法:

DBMS_MVIEW.UNREGISTER_MVIEW (    mviewowner      IN   VARCHAR2,    mviewname       IN   VARCHAR2,    mviewsite       IN   VARCHAR2);

参数介绍:

mviewowner

Owner of the materialized view

mviewname

Name of the materialized view

mviewsite

Name of the materialized view site

PURGE_MVIEW_FROM_LOG Procedure

原文:

This procedure is called on the master site or master materialized view site to delete the rows in materialized view refresh related data dictionary tables maintained at the master for the specified materialized view identified by its mview_id or the combination of the mviewowner, mviewname, and the mviewsite. If the materialized view specified is the oldest materialized view to have refreshed from any of the master tables or master materialized views, then the materialized view log is also purged. This procedure does not unregister the materialized view.

语法:

DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (    mview_id       IN   BINARY_INTEGER  |    mviewowner     IN   VARCHAR2,    mviewname      IN   VARCHAR2,     mviewsite      IN   VARCHAR2);

This procedure is overloaded. The mview_id parameter is mutually exclusive with the three remaining parameters: mviewowner, mviewname, and mviewsite.

参数介绍:

`mview_id`	If you want to execute this procedure based on the identification of the target materialized view, specify the materialized view identification using the `mview_id` parameter. Query the `DBA_BASE_TABLE_MVIEWS` view at the materialized view log site for a listing of materialized view IDs. Executing this procedure based on the materialized view identification is useful if the target materialized view is not listed in the list of registered materialized views (`DBA_REGISTERED_MVIEWS`).
`mviewowner`	If you do not specify an `mview_id`, enter the owner of the target materialized view using the `mviewowner` parameter. Query the `DBA_REGISTERED_MVIEWS` view at the materialized view log site to view the materialized view owners.
`mviewname`	If you do not specify an `mview_id`, enter the name of the target materialized view using the `mviewname` parameter. Query the `DBA_REGISTERED_MVIEWS` view at the materialized view log site to view the materialized view names.
`mviewsite`	If you do not specify an `mview_id`, enter the site of the target materialized view using the `mviewsite` parameter. Query the `DBA_REGISTERED_MVIEWS` view at the materialized view log site to view the materialized view sites.

注意:

If there is an error while purging one of the materialized view logs, the successful purge operations of the previous materialized view logs are not rolled back. This is to minimize the size of the materialized view logs. In case of an error, this procedure can be invoked again until all the materialized view logs are purged.

相关视图介绍:

主站点:

ALL_BASE_TABLE_MVIEWS

原文:

describes the materialized views using materialized view logs accessible to the current user. A materialized view log can be created for a master, base table, or master materialized view. Query this view at the master site or the master materialized view site to show one row for each materialized view using a materialized view log.

字段介绍:

`OWNER`	`VARCHAR2(30)`	Schema in which the master table or the master materialized view was created
`MASTER`	`VARCHAR2(30)`	Name of the master table or the master materialized view
`MVIEW_LAST_REFRESH_TIME`	`DATE`	Date when the materialized view based on the master was last refreshed
`MVIEW_ID`	`NUMBER`	Unique identifier of the materialized view that is based on the master

ALL_MVIEW_LOGS

原文:

describes all materialized view logs accessible to the current user.

字段介绍:

`LOG_OWNER`	`VARCHAR2(30)`	Owner of the materialized view log
`MASTER`	`VARCHAR2(30)`	Name of the master table or master materialized view whose changes are logged
`LOG_TABLE`	`VARCHAR2(30)`	Name of the table where the changes to the master table or master materialized view are logged
`LOG_TRIGGER`	`VARCHAR2(30)`	Obsolete with the release of Oracle8i and higher. Set to `NULL`. Formerly, this parameter was an after-row trigger on the master which inserted rows into the log.
`ROWIDS`	`VARCHAR2(3)`	If `YES`, records rowid information
`PRIMARY_KEY`	`VARCHAR2(3)`	If `YES`, records primary key information
`OBJECT_ID`	`VARCHAR2(3)`	If `YES`, records object identifier information in an object table
`FILTER_COLUMNS`	`VARCHAR2(3)`	If `YES`, records filter columns
`SEQUENCE`	`VARCHAR2(3)`	If `YES`, records the sequence value, which provides additional ordering information
`INCLUDE_NEW_VALUES`	`VARCHAR2(3)`	If `YES`, records both old and new values. If `NO`, records old values, but does not record new values.

DBA_REGISTERED_MVIEW_GROUPS

原文:

lists all the registered materialized view groups at the master site or master materialized view site.

字段介绍：

`NAME`	`VARCHAR2(30)`	Name of the materialized view replication group
`MVIEW_SITE`	`VARCHAR2(128)`	Site of the materialized view replication group
`GROUP_COMMENT`	`VARCHAR2(80)`	Description of the materialized view replication group
`VERSION`	`VARCHAR2(8)`	Oracle release of the materialized view replication group: `ORACLE 7` `ORACLE 8` `REPAPI` Note: Oracle8i and newer materialized view groups show `ORACLE 8`.
`FNAME`	`VARCHAR2(30)`	Name of the flavor of the materialized view group
`OWNER`	`VARCHAR2(30)`	Owner of the materialized view replication group

ALL_REGISTERED_MVIEWS

原文:

describes all registered materialized views (registered at a master site or a master materialized view site) accessible to the current user.

字段介绍:

`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the materialized view
`NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the materialized view
`MVIEW_SITE`	`VARCHAR2(128)`	`NOT NULL`	Global name of the materialized view site
`CAN_USE_LOG`	`VARCHAR2(3)`		`YES` if the materialized view can use a materialized view log, `NO` if the materialized view is too complex to use a log.
`UPDATABLE`	`VARCHAR2(3)`		(`YES`/`NO`) Indicates whether the materialized view is updatable. If set to `NO`, then the materialized view is read only.
`REFRESH_METHOD`	`VARCHAR2(11)`		Whether the materialized view uses primary key, rowids, or object identifiers for fast refresh
`MVIEW_ID`	`NUMBER(38)`		Identifier for the materialized view used by the masters for fast refresh
`VERSION`	`VARCHAR2(26)`		Oracle version of the materialized view Note: Oracle Database materialized views show `ORACLE 8 MATERIALIZED VIEW`.
`QUERY_TXT`	`LONG`		Query that defines the materialized view

物化视图站点:

ALL_MVIEW_REFRESH_TIMES

原文:

describes refresh times of the materialized views accessible to the current user.

字段介绍:

`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the materialized view
`NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the materialized view
`MASTER_OWNER`	`VARCHAR2(30)`		Owner of the master table
`MASTER`	`VARCHAR2(30)`		Name of the master table
`LAST_REFRESH`	`DATE`		The last refresh

ALL_MVIEWS

原文:

describes all materialized views accessible to the current user.

字段介绍:

`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Schema in which the materialized view was created
`MVIEW_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the materialized view
`CONTAINER_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the container in which the materialized view's data is held. Normally this is the same as `MVIEW_NAME`. For materialized views created prior to Oracle8i, the Oracle Database attaches the 6-byte prefix `SNAP$_`. If `MVIEW_NAME` has more than 19 bytes, then the Oracle Database truncates the name to 19 bytes and may add a 4-byte sequence number as a suffix to produce a nonambiguous `CONTAINER_NAME`.
`QUERY`	`LONG`		Query that defines the materialized view
`QUERY_LEN`	`NUMBER(38)`		Length (in bytes) of the defining query
`UPDATABLE`	`VARCHAR2(1)`		Indicates whether the materialized view is updatable (`Y`) or not (`N`)
`UPDATE_LOG`	`VARCHAR2(30)`		For updatable materialized views, the filename of the update log
`MASTER_ROLLBACK_SEG`	`VARCHAR2(30)`		Rollback segment for the master site or the master materialized view site
`MASTER_LINK`	`VARCHAR2(128)`		Database link for the master site or the master materialized view site
`REWRITE_ENABLED`	`VARCHAR2(1)`		Indicates whether rewrite is enabled (`Y`) or not (`N`)
`REWRITE_CAPABILITY`	`VARCHAR2(9)`		Indicates whether the materialized view is eligible for rewrite, and if so, what rules must be followed: `NONE` - Materialized view cannot be used for rewrite, because rewrite is disallowed or prevented `TEXTMATCH` - Defining query of the materialized view contained restrictions on the use of query rewrite `GENERAL` - Defining query of the materialized view contained no restrictions on the use of query rewrite, so the Oracle Database can apply any rewrite rule that is supported
`REFRESH_MODE`	`VARCHAR2(6)`		Refresh mode of the materialized view: `DEMAND` - Oracle Database refreshes this materialized view whenever an appropriate refresh procedure is called `COMMIT` - Oracle Database refreshes this materialized view when a transaction on one of the materialized view's masters commits `NEVER` - Oracle Database never refreshes this materialized view
`REFRESH_METHOD`	`VARCHAR2(8)`		Default method used to refresh the materialized view (can be overridden through the API): `COMPLETE` (`C`) - Materialized view is completely refreshed from the masters `FORCE` (`?`) - Oracle Database performs a fast refresh if possible, otherwise a complete refresh `FAST` (`F`) - Oracle Database performs an incremental refresh applying changes that correspond to changes in the masters since the last refresh `NEVER` (`N`) - User specified that the Oracle Database should not refresh this materialized view
`BUILD_MODE`	`VARCHAR2(9)`		Indicates how the materialized view was populated during creation: `IMMEDIATE` - Populated from the masters during creation `DEFERRED` - Not populated during creation. Must be explicitly populated later by the user. `PREBUILT` - Populated with an existing table during creation. The relationship of the contents of this prebuilt table to the materialized view's masters is unknown to the Oracle Database.
`FAST_REFRESHABLE`	`VARCHAR2(18)`		Indicates whether the materialized view is eligible for incremental (fast) refresh. The Oracle Database calculates this value statically, based on the materialized view definition query: `NO` - Materialized view is not fast refreshable, and hence is complex `DIRLOAD` - Fast refresh is supported only for direct loads `DML` - Fast refresh is supported only for DML operations `DIRLOAD_DML` - Fast refresh is supported for both direct loads and DML operations `DIRLOAD_LIMITEDDML` - Fast refresh is supported for direct loads and a subset of DML operations
`LAST_REFRESH_TYPE`	`VARCHAR2(8)`		Method used for the most recent refresh: `COMPLETE` - Most recent refresh was complete `FAST` - Most recent refresh was fast (incremental) `NA` - Materialized view has not yet been refreshed (for example, if it was created `DEFERRED`)
`LAST_REFRESH_DATE`	`DATE`		Date on which the materialized view was most recently refreshed. Blank if not yet populated.
`STALENESS`	`VARCHAR2(19)`		Relationship between the contents of the materialized view and the contents of the materialized view's masters: `FRESH` - Materialized view is a read-consistent view of the current state of its masters `STALE` - Materialized view is out of date because one or more of its masters has changed. If the materialized view was `FRESH` before it became `STALE`, then it is a read-consistent view of a former state of its masters. `UNUSABLE` - Materialized view is not a read-consistent view of its masters from any point in time `UNKNOWN` - Oracle Database does not know whether the materialized view is in a read-consistent view of its masters from any point in time (this is the case for materialized views created on prebuilt tables) `UNDEFINED` - Materialized view has remote masters. The concept of staleness is not defined for such materialized views.
`AFTER_FAST_REFRESH`	`VARCHAR2(19)`		Specifies the staleness value that will occur if a fast refresh is applied to this materialized view. Its values are the same as for the `STALENESS` column, plus the value `NA`, which is used when fast refresh is not applicable to this materialized view.
`UNKNOWN_PREBUILT`	`VARCHAR2(1)`		Indicates whether the materialized view is prebuilt (`Y`) or not (`N`)
`UNKNOWN_PLSQL_FUNC`	`VARCHAR2(1)`		Indicates whether the materialized view contains PL/SQL functions (`Y`) or not (`N`)
`UNKNOWN_EXTERNAL_TABLE`	`VARCHAR2(1)`		Indicates whether the materialized view contains external tables (`Y`) or not (`N`)
`UNKNOWN_CONSIDER_FRESH`	`VARCHAR2(1)`		Indicates whether the materialized view is considered fresh (`Y`) or not (`N`)
`UNKNOWN_IMPORT`	`VARCHAR2(1)`		Indicates whether the materialized view is imported (`Y`) or not (`N`)
`UNKNOWN_TRUSTED_FD`	`VARCHAR2(1)`		Indicates whether the materialized view uses trusted constraints for refresh (`Y`) or not (`N`)
`COMPILE_STATE`	`VARCHAR2(19)`		Validity of the materialized view with respect to the objects upon which it depends: `VALID` - Materialized view has been validated without error, and no object upon which it depends has changed since the last validation `NEEDS_COMPILE` - Some object upon which the materialized view depends has changed. An `ALTER MATERIALIZED VIEW...COMPILE` statement is required to validate this materialized view. `ERROR` - Materialized view has been validated with one or more errors
`USE_NO_INDEX`	`VARCHAR2(1)`		Indicates whether the materialized view was created using the `USING NO INDEX` clause (`Y`) or the materialized view was created with the default index (`N`). The `USING NO INDEX` clause suppresses the creation of the default index.
`STALE_SINCE`	`DATE`		Time from when the materialized view became stale