| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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.
See Also:
|
This chapter contains the following topics:
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 procedure 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.
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 |
|---|---|
|
|
Owner of the table being reorganized. |
|
|
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 |
|---|---|
|
|
Owner of the table being reorganized. |
|
|
Name of the table being reorganized. |
This procedure estimates the size of a materialized view that you might create, in bytes and number of rows.
DBMS_MVIEW.ESTIMATE_MVIEW_SIZE ( stmt_id IN VARCHAR2, select_clause IN VARCHAR2, num_rows OUT NUMBER, num_bytes OUT NUMBER);
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 or CREATE MATERIALIZED VIEW statement 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.
The procedure is overloaded:
MV_CAPABILITIES_TABLE.VARRAY:DBMS_MVIEW.EXPLAIN_MVIEW ( mv IN VARCHAR2, statement_id IN VARCHAR2:= NULL); DBMS_MVIEW.EXPLAIN_MVIEW ( mv IN VARCHAR2, msg_array OUT SYS.ExplainMVArrayType);
You must run the utlxmv.sql script to create MV_CAPABILITIES_TABLE in the current schema prior to calling EXPLAIN_MVIEW except when you direct output to a VARRAY. The script is found in the admin directory.
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.
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 | CLOB], 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 | CLOB], mv IN VARCHAR2, msg_array IN OUT SYS.RewriteArrayType);
To obtain the output into a table, you must run the utlxrw.sql script before calling EXPLAIN_REWRITE. This script creates a table named REWRITE_TABLE in the current schema.
This function returns the value of the I_AM_REFRESH package state.
DBMS_MVIEW.I_AM_A_REFRESH RETURN BOOLEAN;
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.
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 |
|---|---|
|
|
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:
Oracle Data Warehousing Guide for more information |
DBMS_MVIEW.PURGE_DIRECT_LOAD_LOG();
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.
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 |
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.
This procedure refreshes a list of materialized views.
DBMS_MVIEW.REFRESH ( { list IN VARCHAR2, | tab IN 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, nested IN BOOLEAN := false);
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 DBMS_UTILITY.UNCL_ARRAY,} method IN VARCHAR2 := NULL, rollback_seg IN VARCHAR2 := NULL, refresh_after_errors IN BOOLEAN := false, atomic_refresh IN BOOLEAN := true, nested IN BOOLEAN := false);
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 that, 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.
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 |
|---|---|
|
|
Owner of the materialized view. |
|
|
Name of the materialized view. |
|
|
Name of the materialized view site. |
|
 Copyright © 1998, 2003 Oracle Corporation All Rights Reserved. |
|