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.
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.
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来标识一个物化视图。(因为往往是有很多个物化视图站点的,而站点名可能重复)
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
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 |
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 |
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 |
UNREGISTER_MVIEW_REPGROUP Procedure
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);
参数介绍:
|
Owner of the materialized view |
|
Name of the materialized view |
|
Name of the materialized view site |
PURGE_MVIEW_FROM_LOG Procedure
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
.
参数介绍:
|
If you want to execute this procedure based on the identification of the target materialized view, specify the materialized view identification using the 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 ( |
|
If you do not specify an |
|
If you do not specify an |
|
If you do not specify an |
注意:
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
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:
Note: Oracle8i and newer materialized view groups show |
|
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
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 |
|
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:
|
|
REFRESH_MODE |
VARCHAR2(6) |
Refresh mode of the materialized view:
|
|
REFRESH_METHOD |
VARCHAR2(8) |
Default method used to refresh the materialized view (can be overridden through the API):
|
|
BUILD_MODE |
VARCHAR2(9) |
Indicates how the materialized view was populated during creation:
|
|
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:
|
|
LAST_REFRESH_TYPE |
VARCHAR2(8) |
Method used for the most recent refresh:
|
|
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:
|
|
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:
|
|
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 |