Skip Headers

Oracle9i Replication Management API Reference
Release 2 (9.2)

Part Number A96568-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

15
DBMS_MVIEW

DBMS_MVIEW enables you to understand capabilities for materialized views and potential materialized views, including their rewrite availability. It also enables you to refresh materialized views that are not part of the same refresh group and purge logs.

This chapter discusses the following topics:


Summary of DBMS_MVIEW Subprograms

Table 15-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"

Consistently 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.


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.

See Also:

"Reorganizing Master Tables that Have Materialized View Logs"

Syntax

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

Parameters

Table 15-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.

See Also:

"Reorganizing Master Tables that Have Materialized View Logs"

Syntax

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

Parameters

Table 15-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 15-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 15-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;

Parameters

None.


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 15-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();

Parameters

None.



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 15-7 PURGE_LOG Procedure Parameters
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');


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 15-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 15-9 REFRESH Procedure Parameters  
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:

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 15-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:

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 15-11 REFRESH_DEPENDENT Procedure Parameters  
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 15-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 15-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.


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback