Release 2 (9.2)
Part Number A96568-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Replication Management API Reference Release 2 (9.2) Part Number A96568-01 |
|
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:
See Also:
|
This procedure performs a process to preserve materialized view data needed for refresh. It must be called before a master table is reorganized.
DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION ( tabowner IN VARCHAR2, tabname IN VARCHAR2);
| Parameter | Description |
|---|---|
tabowner |
Owner of the table being reorganized. |
tabname |
Name of the table being reorganized. |
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.
DBMS_MVIEW.END_TABLE_REORGANIZATION ( tabowner IN VARCHAR2, tabname IN VARCHAR2);
| Parameter | Description |
|---|---|
tabowner |
Owner of the table being reorganized. |
tabname |
Name of the table being reorganized. |
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.
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);
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.
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);
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.
DBMS_MVIEW.I_AM_A_REFRESH() RETURN BOOLEAN;
None.
This function returns a partition marker from a rowid. It is used for Partition Change Tracking (PCT).
DBMS_MVIEW.PMARKER(rid IN ROWID) RETURN NUMBER;
| Parameter | Description |
|---|---|
rid |
The rowid of a row entry in a master table. |
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 |
DBMS_MVIEW.PURGE_DIRECT_LOAD_LOG();
None.
This procedure purges rows from the materialized view log.
DBMS_MVIEW.PURGE_LOG ( master IN VARCHAR2, num IN BINARY_INTEGER := 1, flag IN VARCHAR2 := 'NOP');
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.
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 |
This procedure refreshes a list of materialized views.
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);
This procedure refreshes all materialized views that have the following properties:
DBA_MVIEWS.This procedure is intended for use with data warehouses.
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);
This procedure refreshes all materialized views that have the following properties:
DBA_MVIEWS.This procedure is intended for use with data warehouses.
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);
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.
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);
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.
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);
| Parameters | Description |
|---|---|
mviewowner |
Owner of the materialized view. |
mviewname |
Name of the materialized view. |
mviewsite |
Name of the materialized view site. |
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|