DBMS_MVIEW, 2 of 2

Summary of DBMS_MVIEW Subprograms

Table 33-1 DBMS_MVIEW Package Subprograms

Subprogram	Description
BEGIN_TABLE_REORGANIZATION Procedure	Performs a process to preserve materialized view data needed for refresh.
END_TABLE_REORGANIZATION Procedure	Ensures that the materialized view data for the master table is valid and that the master table is in the proper state.
EXPLAIN_MVIEW Procedure	Explains what is possible with a materialized view or potential materialized view.
EXPLAIN_REWRITE Procedure	Explains why a query failed to rewrite.
I_AM_A_REFRESH Function	Returns the value of the `I_AM_REFRESH` package state.
PMARKER Function	Returns a partition marker from a rowid. This function is used for Partition Change Tracking (PCT).
PURGE_DIRECT_LOAD_LOG Procedure	Purges rows from the direct loader log after they are no longer needed by any materialized views (used with data warehousing).
PURGE_LOG Procedure	Purges rows from the materialized view log.
PURGE_MVIEW_FROM_LOG Procedure	Purges rows from the materialized view log.
REFRESH Procedure	Refreshes one or more materialized views that are not members of the same refresh group.
REFRESH_ALL_MVIEWS Procedure	Refreshes all materialized views that do not reflect changes to their master table or master materialized view.
REFRESH_DEPENDENT Procedure	Refreshes all table-based materialized views that depend on a specified master table or master materialized view, or list of master tables or master materialized views.
REGISTER_MVIEW Procedure	Enables the administration of individual materialized views.
UNREGISTER_MVIEW Procedure	Enables the administration of individual materialized views. Invoked at a master site or master materialized view site to unregister a materialized view.

Note:

If a query is less than 256 characters long, you can invoke EXPLAIN_REWRITE()using the EXECUTE command from SQL*PLUS. Otherwise, the recommended method is to use a PL/SQL BEGIN..END block, as shown in the examples in /rdbms/demo/smxrw.sql. The EXPLAIN_REWRITE()API cannot accept queries longer than 32627 characters. These restrictions also apply when passing the defining query of a materialized view to the EXPLAIN_MVIEW procedure.

BEGIN_TABLE_REORGANIZATION Procedure

This procedure performs a process to preserve materialized view data needed for refresh. It must be called before a master table is reorganized.

Syntax

DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION (
   tabowner    IN   VARCHAR2,
   tabname     IN   VARCHAR2);

Parameters

Table 33-2 BEGIN_TABLE_REORGANIZATION Procedure Parameters

Parameter	Description
tabowner	Owner of the table being reorganized.
tabname	Name of the table being reorganized.

END_TABLE_REORGANIZATION Procedure

This procedure ensures that the materialized view data for the master table is valid and that the master table is in the proper state. It must be called after a master table is reorganized.

Syntax

DBMS_MVIEW.END_TABLE_REORGANIZATION (
   tabowner    IN   VARCHAR2,
   tabname     IN   VARCHAR2);

Parameters

Table 33-3 END_TABLE_REORGANIZATION Procedure Parameters

Parameter	Description
tabowner	Owner of the table being reorganized.
tabname	Name of the table being reorganized.

EXPLAIN_MVIEW Procedure

This procedure enables you to learn what is possible with a materialized view or potential materialized view. For example, you can determine if a materialized view is fast refreshable and what types of query rewrite you can perform with a particular materialized view.

Using this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW, passing in as parameters the schema and materialized view name for an existing materialized view. Alternatively, you can specify the SELECT string for a potential materialized view. The materialized view or potential materialized view is then analyzed and the results are written into either a table called MV_CAPABILITIES_TABLE, which is the default, or to an array called MSG_ARRAY.

Note that you must run the utlxmv.sql script prior to calling EXPLAIN_MVIEW except when you direct output to a VARRAY. The script is found in the admin directory. In addition, you must create MV_CAPABILITIES_TABLE in the current schema.

Syntax

The following PL/SQL declarations that are made for you in the DBMS_MVIEW package show the order and datatypes of these parameters for explaining an existing materialized view and a potential materialized view with output to a table and to a VARRAY.

To explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE:

DBMS_MVIEW.EXPLAIN_MVIEW (
 mv            IN VARCHAR2,
 statement_id  IN VARCHAR2:= NULL);

To explain an existing or potential materialized view with output to a VARRAY:

DBMS_MVIEW.EXPLAIN_MVIEW (
 mv          IN VARCHAR2,
 msg_array   OUT SYS.ExplainMVArrayType);

Parameters

Table 33-4 EXPLAIN_MVIEW Procedure Parameters

Parameter	Description
mv	The name of an existing materialized view (optionally qualified with the owner name separated by a ".") or a `SELECT` statement for a potential materialized view.
statement_id	A client-supplied unique identifier to associate output rows with specific invocations of `EXPLAIN_MVIEW`.
msg_array	The PL/SQL varray that receives the output. Use this parameter to direct `EXPLAIN_MVIEW`'s output to a PL/SQL VARRAY rather than `MV_CAPABILITIES_TABLE`.

EXPLAIN_REWRITE Procedure

This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, which materialized views will be used. Using the results from the procedure, you can take the appropriate action needed to make a query rewrite if at all possible. The query specified in the EXPLAIN_REWRITE statement is never actually executed.

To obtain the output into a table, you must run the admin/utlxrw.sql script before calling EXPLAIN_REWRITE. This script creates a table named REWRITE_TABLE in the current schema.

Syntax

You can obtain the output from EXPLAIN_REWRITE in two ways. The first is to use a table, while the second is to create a VARRAY. The following shows the basic syntax for using an output table:

DBMS_MVIEW.EXPLAIN_REWRITE (
    query           IN VARCHAR2,
    mv              IN VARCHAR2,
    statement_id    IN VARCHAR2;

If you want to direct the output of EXPLAIN_REWRITE to a varray, instead of a table, then the procedure should be called as follows:

DBMS_MVIEW.EXPLAIN_REWRITE (
    query           IN VARCHAR2(2000),
    mv              IN VARCHAR2(30),
    msg_array       IN OUT SYS.RewriteArrayType);

Parameters

Table 33-5 EXPLAIN_REWRITE Procedure Parameters

Parameter	Description
query	SQL select statement to be explained.
mv	The fully qualified name of an existing materialized view in the form of SCHEMA.MV
statement_id	A client-supplied unique identifier to distinguish output messages
msg_array	The PL/SQL varray that receives the output. Use this parameter to direct `EXPLAIN_REWRITE`'s output to a PL/SQL VARRAY

I_AM_A_REFRESH Function

This function returns the value of the I_AM_REFRESH package state. A return value of TRUE indicates that all local replication triggers for materialized views are effectively disabled in this session because each replication trigger first checks this state. A return value of FALSE indicates that these triggers are enabled.

Syntax

DBMS_MVIEW.I_AM_A_REFRESH()
  RETURN BOOLEAN;

PMARKER Function

This function returns a partition marker from a rowid. It is used for Partition Change Tracking (PCT).

Syntax

DBMS_MVIEW.PMARKER(rid IN ROWID)
  RETURN NUMBER;

Parameters

Table 33-6 PMARKER Procedure Parameters

Parameter	Description
rid	The rowid of a row entry in a master table.

PURGE_DIRECT_LOAD_LOG Procedure

This procedure removes entries from the direct loader log after they are no longer needed for any known materialized view. This procedure usually is used in environments using Oracle's data warehousing technology.

See Also:

Oracle9i Data Warehousing Guide for more information

Syntax

DBMS_MVIEW.PURGE_DIRECT_LOAD_LOG();

PURGE_LOG Procedure

This procedure purges rows from the materialized view log.

Syntax

DBMS_MVIEW.PURGE_LOG (
   master        IN   VARCHAR2,
   num           IN   BINARY_INTEGER := 1,
   flag          IN   VARCHAR2       := 'NOP');

Parameters

Table 33-7 PURGE_LOG Procedure Parameters

Parameter Description

Parameter	Description
master	Name of the master table or master materialized view.
num	Number of least recently refreshed materialized views whose rows you want to remove from materialized view log. For example, the following statement deletes rows needed to refresh the two least recently refreshed materialized views: `DBMS_MVIEW.PURGE_LOG('master_table', 2);` To delete all rows in the materialized view log, indicate a high number of materialized views to disregard, as in this example: `DBMS_MVIEW.PURGE_LOG('master_table',9999);` This statement completely purges the materialized view log that corresponds to `master_table` if fewer than 9999 materialized views are based on `master_table`. A simple materialized view whose rows have been purged from the materialized view log must be completely refreshed the next time it is refreshed.
flag	Specify `delete` to guarantee that rows are deleted from the materialized view log for at least one materialized view. This parameter can override the setting for the parameter `num`. For example, the following statement deletes rows from the materialized view log that has dependency rows in the least recently refreshed materialized view: `DBMS_MVIEW.PURGE_LOG('master_table',1,'delete');`

master

Name of the master table or master materialized view.

num

Number of least recently refreshed materialized views whose rows you want to remove from materialized view log. For example, the following statement deletes rows needed to refresh the two least recently refreshed materialized views:

DBMS_MVIEW.PURGE_LOG('master_table', 2);

To delete all rows in the materialized view log, indicate a high number of materialized views to disregard, as in this example:

DBMS_MVIEW.PURGE_LOG('master_table',9999);

This statement completely purges the materialized view log that corresponds to master_table if fewer than 9999 materialized views are based on master_table. A simple materialized view whose rows have been purged from the materialized view log must be completely refreshed the next time it is refreshed.

flag

Specify delete to guarantee that rows are deleted from the materialized view log for at least one materialized view. This parameter can override the setting for the parameter num. For example, the following statement deletes rows from the materialized view log that has dependency rows in the least recently refreshed materialized view:

DBMS_MVIEW.PURGE_LOG('master_table',1,'delete');

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.

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.

Syntax

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

Note:

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

Parameters

Table 33-8 PURGE_MVIEW_FROM_LOG Procedure Parameters

Parameter	Description
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 a `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 a `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 a `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.

REFRESH Procedure

This procedure refreshes a list of materialized views.

Syntax

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true);

Note:

This procedure is overloaded. The list and tab parameters are mutually exclusive.

Parameters

Table 33-9 REFRESH Procedure Parameters (Page 1 of 2)

Parameter	Description
list \| tab	Comma-separated list of materialized views that you want to refresh. (Synonyms are not supported.) These materialized views can be located in different schemas and have different master tables or master materialized views. However, all of the listed materialized views must be in your local database. Alternatively, you may pass in a PL/SQL index-by table of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a materialized view.
method	A string of refresh methods indicating how to refresh the listed materialized views. An `f` indicates fast refresh, `?` indicates force refresh, `C` or `c` indicates complete refresh, and `A` or `a` indicates always refresh. `A` and `C` are equivalent. If a materialized view does not have a corresponding refresh method (that is, if more materialized views are specified than refresh methods), then that materialized view is refreshed according to its default refresh method. For example, consider the following `EXECUTE` statement within SQL*Plus: DBMS_MVIEW.REFRESH ('countries_mv,regions_mv,hr.employees_mv','cf'); This statement performs a complete refresh of the `countries_mv` materialized view, a fast refresh of the `regions_mv` materialized view, and a default refresh of the `hr.employees` materialized view.
rollback_seg	Name of the materialized view site rollback segment to use while refreshing materialized views.
push_deferred_rpc	Used by updatable materialized views only. Set this parameter to `true` if you want to push changes from the materialized view to its associated master tables or master materialized views before refreshing the materialized view. Otherwise, these changes may appear to be temporarily lost.
refresh_after_errors	If this parameter is `true`, an updatable materialized view continues to refresh even if there are outstanding conflicts logged in the `DEFERROR` view for the materialized view's master table or master materialized view. If this parameter is `true` and `atomic_refresh` is `false`, this procedure continues to refresh other materialized views if it fails while refreshing a materialized view.
purge_option	If you are using the parallel propagation mechanism (in other words, parallelism is set to 1 or greater), 0 means do not purge, 1 means lazy purge, and 2 means aggressive purge. In most cases, lazy purge is the optimal setting. Set purge to aggressive to trim the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, then set this parameter to `0` and occasionally execute `PUSH` with this parameter set to `2` to reduce the queue.
parallelism	0 specifies serial propagation. n > 1 specifies parallel propagation with n parallel processes. 1 specifies parallel propagation using only one parallel process.
heap_size	Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Note: Do not set this parameter unless directed to do so by Oracle Support Services.
atomic_refresh	If this parameter is set to `true`, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to `false`, then each of the materialized views is refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is `false`.

REFRESH_ALL_MVIEWS Procedure

This procedure refreshes all materialized views that have the following properties:

The materialized view has not been refreshed since the most recent change to a master table or master materialized view on which it depends.
The materialized view and all of the master tables or master materialized views on which it depends are local.
The materialized view is in the view DBA_MVIEWS.

This procedure is intended for use with data warehouses.

Syntax

DBMS_MVIEW.REFRESH_ALL_MVIEWS (
   number_of_failures     OUT   BINARY_INTEGER,
   method                 IN    VARCHAR2         := NULL,
   rollback_seg           IN    VARCHAR2         := NULL,
   refresh_after_errors   IN    BOOLEAN          := false,
   atomic_refresh         IN    BOOLEAN          := true);

Parameters

Table 33-10 REFRESH_ALL_MVIEWS Procedure Parameters

Parameter	Description
number_of_failures	Returns the number of failures that occurred during processing.
method	A single refresh method indicating the type of refresh to perform for each materialized view that is refreshed. `F` or `f` indicates fast refresh, `?` indicates force refresh, `C` or c indicates complete refresh, and `A` or a indicates always refresh. `A` and `C` are equivalent. If no method is specified, a materialized view is refreshed according to its default refresh method.
rollback_seg	Name of the materialized view site rollback segment to use while refreshing materialized views.
refresh_after_errors	If this parameter is `true`, an updatable materialized view continues to refresh even if there are outstanding conflicts logged in the `DEFERROR` view for the materialized view's master table or master materialized view. If this parameter is `true` and `atomic_refresh` is `false`, this procedure continues to refresh other materialized views if it fails while refreshing a materialized view.
atomic_refresh	If this parameter is set to `true`, then the refreshed materialized views are refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to `false`, then each of the refreshed materialized views is refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is `false`.

REFRESH_DEPENDENT Procedure

This procedure refreshes all materialized views that have the following properties:

The materialized view depends on a master table or master materialized view in the list of specified masters.
The materialized view has not been refreshed since the most recent change to a master table or master materialized view on which it depends.
The materialized view and all of the master tables or master materialized views on which it depends are local.
The materialized view is in the view DBA_MVIEWS.

This procedure is intended for use with data warehouses.

Syntax

DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures     OUT    BINARY_INTEGER,
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2    := NULL,
   rollback_seg           IN     VARCHAR2    := NULL,
   refresh_after_errors   IN     BOOLEAN     := false,
   atomic_refresh         IN     BOOLEAN     := true);

Note:

This procedure is overloaded. The list and tab parameters are mutually exclusive.

Parameters

Table 33-11 REFRESH_DEPENDENT Procedure Parameters (Page 1 of 3)

Parameter	Description
number_of_failures	Returns the number of failures that occurred during processing.
list \| tab	Comma-separated list of master tables or master materialized views on which materialized views can depend. (Synonyms are not supported.) These tables and the materialized views that depend on them can be located in different schemas. However, all of the tables and materialized views must be in your local database. Alternatively, you may pass in a PL/SQL index-by table of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a table.
method	A string of refresh methods indicating how to refresh the dependent materialized views. All of the materialized views that depend on a particular table are refreshed according to the refresh method associated with that table. `F` or `f` indicates fast refresh, ? indicates force refresh, `C` or `c` indicates complete refresh, and `A` or `a` indicates always refresh. `A` and `C` are equivalent. If a table does not have a corresponding refresh method (that is, if more tables are specified than refresh methods), then any materialized view that depends on that table is refreshed according to its default refresh method. For example, the following `EXECUTE` statement within SQL*Plus: DBMS_MVIEW.REFRESH_DEPENDENT ('employees,deptartments,hr.regions','cf'); performs a complete refresh of the materialized views that depend on the `employees` table, a fast refresh of the materialized views that depend on the `departments` table, and a default refresh of the materialized views that depend on the `hr.regions` table.
rollback_seg	Name of the materialized view site rollback segment to use while refreshing materialized views.
refresh_after_errors	If this parameter is `true`, an updatable materialized view continues to refresh even if there are outstanding conflicts logged in the `DEFERROR` view for the materialized view's master table or master materialized view. If this parameter is `true` and `atomic_refresh` is `false`, this procedure continues to refresh other materialized views if it fails while refreshing a materialized view.
atomic_refresh	If this parameter is set to `true`, then the refreshed materialized views are refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to `false`, then each of the refreshed materialized views is refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is `false`.

REGISTER_MVIEW Procedure

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

Note:

Typically, a materialized view is registered automatically during materialized view creation. You should only run this procedure to manually register a materialized view if the automatic registration failed or if the registration information was deleted.

Syntax

DBMS_MVIEW.REGISTER_MVIEW (
   mviewowner  IN   VARCHAR2,
   mviewname   IN   VARCHAR2,
   mviewsite   IN   VARCHAR2,
   mview_id    IN   DATE | BINARY_INTEGER,
   flag        IN   BINARY_INTEGER,
   qry_txt     IN   VARCHAR2,
   rep_type    IN   BINARY_INTEGER := DBMS_MVIEW.REG_UNKNOWN);

Parameters

Table 33-12 REGISTER_MVIEW Procedure Parameters

Parameter	Description
mviewowner	Owner of the materialized view.
mviewname	Name of the materialized view.
mviewsite	Name of the materialized view site for a materialized view registering at an Oracle8 and higher master site or master materialized view site. This name should not contain any double quotes.
mview_id	The identification number of the materialized view. Specify an Oracle8 and higher materialized view as a `BINARY_INTEGER`. Specify an Oracle7 materialized view registering at an Oracle8 and higher master sites or master materialized view sites as a `DATE`.
flag	A constant that describes the properties of the materialized view being registered. Valid constants that can be assigned include the following: `dbms_mview.reg_rowid_mview` for a rowid materialized view `dbms_mview.reg_primary_key_mview` for a primary key materialized view `dbms_mview.reg_object_id_mview` for an object id materialized view `dbms_mview.reg_fast_refreshable_mview` for a materialized view that can be fast refreshed `dbms_mview.reg_updatable_mview` for a materialized view that is updatable A materialized view can have more than one of these properties. In this case, use the plus sign (+) to specify more than one property. For example, if a primary key materialized view can be fast refreshed, you can enter the following for this parameter: dbms_mview.reg_primary_key_mview + dbms_mview.reg_fast_refreshable_mview You can determine the properties of a materialized view by querying the `ALL_MVIEWS` data dictionary view.
qry_txt	The first 32,000 bytes of the materialized view definition query.
rep_type	Version of the materialized view. Valid constants that can be assigned include the following: `dbms_mview.reg_v7_snapshot` if the materialized view is at an Oracle7 site `dbms_mview.reg_v8_snapshot` if the materialized view is at an Oracle8 or higher site `dbms_mview.reg_unknown` (the default) if you do not know whether the materialized view is at an Oracle7 site or an Oracle8 (or higher) site

Usage Notes

This procedure is invoked at the master site or master materialized view site by a remote materialized view site using a remote procedure call. If REGISTER_MVIEW is called multiple times with the same mviewowner, mviewname, and mviewsite, then the most recent values for mview_id, flag, and qry_txt are stored. If a query exceeds the maximum VARCHAR2 size, then qry_txt contains the first 32000 characters of the query and the remainder is truncated. When invoked manually, the value of mview_id must be looked up in the materialized view data dictionary views by the person who calls the procedure.

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.

Syntax

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

Parameters

Table 33-13 UNREGISTER_MVIEW Procedure Parameters

Parameters	Description
mviewowner	Owner of the materialized view.
mviewname	Name of the materialized view.
mviewsite	Name of the materialized view site.