54
DBMS_MVIEW

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


 





Note:  
DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.

 



See Also:  

Oracle Database Advanced Replication for more information about using materialized views
in a replication environment
Oracle Data Warehousing Guide for more information about using materialized views in a data
 warehousing environment
 
This chapter contains the following
topics:

Using DBMS_MVIEW 

Operational Notes

Rules and Limits



Summary of DBMS_MVIEW Subprograms
 

Using DBMS_MVIEW

<
li class="LB1" type="disc">Operational Notes
Rules and Limits
 

Operational Notes
 
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.
 

Rules and Limits
 
The EXPLAIN_REWRITE procedure c
annot accept queries longer than 32627 characters. These restrictions also apply when passing the defining query of a materialized vi
ew to the EXPLAIN_MVIEW procedure.
 

Summary of DBMS_MVIEW Subprograms
 Table 54-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

 
ESTIMATE_MVIEW_SIZE Procedure
 
Estimates the size of a materialized view t
hat you might create, in bytes and rows

 
EXPLAIN_MVIEW Procedure
 
Explains what is possible with a materialized view or potential materialized view

 
EXPLAIN_REWRITE Procedures
 
Explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view 

 
I_AM_A_REFRESH Function
 
Retur
ns the value of the I_AM_REFRESH package state

 
PMARKER Function
 
Returns a partition marker from a rowid, and is used for Partition Change T
racking (PCT)

 
PURGE_DIRECT_LOAD_LOG Procedure
 
Purges rows from the direct loader log after they are no longer needed by any materialized views (used wit
h data warehousing)

 
PURGE_LOG Procedure
 
Purges rows from the materialized view log

 
PURGE_MVIEW_FROM_LOG Procedure
 
Purges rows from the materialized view log

 
RE
FRESH Procedure
 
Refreshes one o
r more materialized views that are not members of the same refresh group

 
REFRESH_ALL_MVIEWS Procedure
 
Refreshes all materialized views that do not refle
ct changes to their master table or master materialized view

 
REFRESH_DEPENDENT Procedure

 
Refreshes all table-based materialized views that depend on a s
pecified master table or master materialized view, or list of master tables or master materialized views

 
REGISTER_MVIEW P
rocedure
 
Enables the administra
tion of individual materialized views

 
UNREGISTER_MVIEW Procedure
 
Enables the administration of individual materialized views once invoked at a master si
te or master materialized view site to unregister a materialized view
  

BEGIN_TABLE_REORGANIZATIO
N Procedure
 
This procedure performs a process to preserve materialized view data nee
ded for refresh. It must be called before a master table is reorganized.
 
Syntax
DBMS_MVIEW.BEGIN_TABLE_REORGANIZATIO
N (
   tabowner    IN   VARCHAR2,
   tabname     IN   VARCHAR2);


<
/a>
Parameters

 Table 54-2 BEGIN_TABLE_REORGANIZATION Procedure Param
eters
<
/thead>



 Parameter
 Description


 
tabowner<
/code>
 
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 vali
d and that the master table is in the proper state. It must be called after a master table is reorganized.


Syntax
DB
MS_MVIEW.END_TABLE_REORGANIZATION (
   tabowner    IN   VARCHAR2,
   tabname     IN   VARCH
AR2);



Parameters
 Table 54-3 END_TABLE_
REORGANIZATION Procedure Parameters



 Parameter
 Descript
ion




tabowner
 
Owner of the table being reorga
nized.

 
tabn
ame
 
Name of the table being reorganized.
 

EST
IMATE_MVIEW_SIZE Procedure
 
This procedure estimates the size of a materialized view
 that you might create, in bytes and number of rows.
 
Syntax
DBMS_MVIEW.ESTIMATE_MVIEW_SIZE (
   stmt_id       IN  VARCHAR2,
   select_clause IN  VARCHAR2,
   num_rows      OUT NUMBE
R,
   num_bytes     OUT NUMBER);



Parameters
 Table 54-4 ESTIMATE_MVIEW_SIZE Procedure Parameters
 
Estimated cardinality.


 Parameter
 Datatype
 Description


 
stmt_id
 
NUMBER
 
Arbitrary string used to identify the statement in an EXPLAIN PLAN.

 
select_clause
 
STRING
 
The SELECT statement to be analyzed.

 
num_rows
 
NUMBER


 
num_bytes
 
NUMBER
 
Estimated number of bytes.
 

EXPLAIN_MVIEW Procedure
 
This procedure enables you to learn what is po
ssible with a materialized view or potential materialized view. For example, you can determine if a materialized view is fast refresh
able and what types of query rewrite you can perform with a particular materialized view.
 
Us
ing this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW, passing in as parameters t
he 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 vie
w or potential materialized view is then analyzed and the results are written into either a table called MV_CAPABILITIES_TABLE<
/code>, which is the default, or to an array called MSG_ARRAY.

 
The procedure i
s overloaded:

The first version is for explaining an existing
or potential materialized view with output to MV_CAPABILITIES_TABLE.

The second version is for explaining an existing or potential materialized view with output to a VARRAY:
<
a name="996981"> 
Syntax
DBMS_MVIEW.EXPLAIN_MVIEW (
   mv            IN VARCHAR2,
   statement_id
  IN VARCHAR2:= NULL);

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



Parameters
 Table 54-5 EXPLAIN_MVIEW Procedure Parameters



 Parameter
 <
font face="Arial, Helvetica, sans-serif">Description


 
mv

 
The name of an existing materialized view (optionally qualified with the owner name separated by a ".") or a SELECT statement or a CREATE MATERIALIZED VIEW statement for a potential materialized
 view.

 
stat
ement_id
 
A client-supplied unique identifier to associate out
put 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 r
ather than MV_CAPABILITIES_TABLE.
 
Usage Notes
 
You must run the utlxmv
.sql script to create MV_CAPABILITIES_TABLE in the current schema prior to calling EXPLAIN_MVIEW exc
ept when you direct output to a VARRAY. The script is found in the admin directory.
 

EXPLAIN_REWRITE Procedures

 
This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, whi
ch 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.
 
Syntax
 
<
p class="BP">You can obtain the output from EXPLAIN_REWRITE in two ways. The first is to use a table, while the second i
s 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 proc
edure should be called as follows:
DBMS_MVIEW.EXPLAIN_REWRITE (
    q
uery           IN [VARCHAR2 | CLOB], 
    mv              IN VARCHAR2,
    msg_array
 IN OUT SYS.RewriteArrayType);



Parameters
 
Table 54-6 EXPLAIN_REWRITE Procedure Parameters



 Parameter
 
Description


 
query
 
SQL SELECT statement to be explained.


 
mv
 
The fully qualified name of an exist
ing 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 VA
RRAY.
 
Usage Notes
 
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.

 

I
_AM_A_REFRESH Function
 
This function returns the value of the I_AM_REFRESH package state.

 
Syntax

DBMS_MVIEW.I_AM_A_REFRESH
   RETURN BOOLEAN;



Return Values
 
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 tha
t these triggers are enabled.
 

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 R
OWID)
 RETURN NUMBER; 



Parameters
 Table 54-7 PMARKER Procedure Parameters



 Parameter
 Description


<
a name="997086"> 
rid
 
The rowid of a
row entry in a master table.
 

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



See Also:  
Oracle Data Warehousing Guide for more information

 
Syntax
DBMS_MVIEW.PURGE_DIRECT_LOAD_LOG();




PURGE_LOG Procedure
 
This pro
cedure 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<
/font>

 Table 54-
8 PURGE_LOG Procedure Parameters
<
/table>
PURGE_
MVIEW_FROM_LOG Procedure
This procedure is called on the master site or master mater
ialized view site to delete the rows in materialized view refresh related data dictionary tables maintained at the master for the spe
cified 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.
 
Syntax

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








 Parameter
 Description



 
master
 
Name of the master table or master m
aterialized view.

 
num
 
Number of least recently refreshed materialized vi
ews 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 materia
lized 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 r
ows 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 vie
w log for at least one materialized view. This parameter can override the setting for the parameter num. For example, th
e following statement deletes rows from the materialized view log that has dependency rows in the least recently refreshed materializ
ed view: 
DBMS_MVIEW.PURGE_LOG('master_table',1,'delete'); 


 





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


 
Parameters
 Table 54-9 PURGE_MVIEW_FROM_LOG Procedure Parameters



 Para
meter
 Description


 
mview_id
 
If you want to execute this procedure based on the identification of the target materialized view, specify the materialized view i
dentification using the mview_id parameter. Query the DBA_BASE_TABLE_MVIEWS view at the materialized view l
og site for a listing of materialized view IDs. 
Executing this procedure based on the materia
lized 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_RE
GISTERED_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 u
sing the mviewname parameter. Query the DBA_REGISTERED_MVIEWS view at the materialized view log site to vie
w the materialized view names.

 
mviewsite
 
If you do not specify a m
view_id, enter the site of the target materialized view using the mviewsite parameter. Query the DBA_REGIST
ERED_MVIEWS view at the materialized view log site to view the materialized view sites.
 
Usage Notes
<
/a> 
If there is an error while purging one of the materialized view logs, the successful purge operations of the previ
ous 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.
 

REFRESH Procedure
 
This procedure refreshes a list of materialized views.
 
Syntax
DBMS_MVIEW.REFRESH (
   { li
st                 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,
   para
llelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN
    := false);









Not
e:  
This procedure is overloaded. The list and tab para
meters are mutually exclusive.

 
Parameters
 Table 54-10  REFRESH Procedure Parameters



 Parameter
 Description


 
list | tab
 
Comma-delimited list of materialized views that you want to refresh. (Synonyms are not supported.) These materialized vie
ws can be located in different schemas and have different master tables or master materialized views. However, all of the listed mate
rialized 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 re
fresh, and A or a indicates always refresh. A and C are equivalent. P or p refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables. <
a name="997242">
If a materialized view does not have a corresponding refresh method (that is, if more material
ized 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 s
tatement 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
<
a name="997249"> 
Name of the materialized view site rollback segment to use while refreshing materialized views.

 
push_deferre
d_rpc
 
Used by updatable materialized views only. Set this par
ameter to true if you want to push changes from the materialized view to its associated master tables or master material
ized views before refreshing the materialized view. Otherwise, these changes may appear to be temporarily lost.

 
refresh_after_errors<
/td>
 
If this parameter is true, an updatable materialized view c
ontinues to refresh even if there are outstanding conflicts logged in the DEFERROR view for the materialized view's mast
er table or master materialized view. If this parameter is true and atomic_refresh is false, t
his 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 i
s 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 opti
mal setting. Set purge to aggressive to trim the queue if multiple master replication groups are pushed to different target sites, an
d updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently update
d 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.<
/td>

 
heap_size
 
Maximum number of transactions to be examined simultaneously for p
arallel 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_refres
h
 
If this parameter is set to true, then the lis
t of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point i
n 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 transac
tion.

 
nest
ed
 
If true, then perform nested refresh operati
ons for the specified set of materialized views. Nested refresh operations refresh all the depending materialized views and the speci
fied set of materialized views based on a dependency order to ensure the nested materialized views are truly fresh with respect to th
e underlying base tables.
 

REFRESH_ALL_MVIEWS Procedure
 
This procedur
e 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 wh
ich it depends.
The materialized view and all of the master tables or master ma
terialized 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    VARCHAR
2         := NULL,
   refresh_after_errors   IN    BOOLEAN          := false,
   atomic_ref
resh         IN    BOOLEAN          := true);



Parameters
 Table 54-11  REFRESH_ALL_MVIEWS Procedure Parameters



 Parameter
 Description


 
number_of_failures

 
Returns the number of failures that occurred during processing.

 
method
<
/a> 
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 specifi
ed, a materialized view is refreshed according to its default refresh method. P or p refreshes by recomputi
ng the rows in the materialized view affected by changed partitions in the detail tables.

 
rollback_seg
 
Name of the materialized view site rollback segment to use while refreshing materialized views.

 
refresh_after_err
ors
 
If this parameter is true, an updatable mate
rialized view continues to refresh even if there are outstanding conflicts logged in the DEFERROR view for the materiali
zed 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 t
ime. 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.
 

REFRESH_DEPENDENT Procedure
 
This procedure refreshes al
l materialized views that have the following properties:

The ma
terialized 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 materi
alized view on which it depends.
The materialized view and all of the master ta
bles or master materialized views on which it depends are local.
The materializ
ed view is in the view DBA_MVIEWS.
 
This procedure is intended for use wit
h data warehouses.
 
Syntax

DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures     OUT    BINAR
Y_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,
<
/a>   atomic_refresh         IN     BOOLEAN     := true,
   nested                 IN     BOOLEAN     := false)
;









Note:  
This procedure is overloaded. The list and tab parameters are mu
tually exclusive.

 
Parameters
 Table 54-12  REFRESH_DEPENDENT Procedure Parameters




 
Parameter
 Description


 
number_of_failures
 
Returns the number of failures that occurred during processing.

 
list | tab

 
Comma-delimited list of master tables or master materialized views on which materialized views can depend. (Synon
yms 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 materiali
zed views. All of the materialized views that depend on a particular table are refreshed according to the refresh method associated w
ith 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 equi
valent. P or p refreshes by recomputing the rows in the materialized view affected by changed partitions in
 the detail tables. 
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 defa
ult 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 material
ized 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 ar
e 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 o
ther 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 trans
action. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materializ
ed 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.

 
nested
 
If true, then perform nested refresh operations for the specified set of tables. Nested
refresh operations refresh all the depending materialized views of the specified set of tables based on a dependency order to ensure
the nested materialized views are truly fresh with respect to the underlying base tables.
 

REGISTER_MVIEW Procedure

 
This procedure enables the administration of individual materialized views. It is invo
ked 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 procedu
re to manually register a materialized view if the automatic registration failed or if the registration information was deleted.
 
Syntax
<
a name="997413">DBMS_MVIEW.REGISTER_MVIEW (
   mviewowner  IN   VARCHAR2,
   mviewname
  IN   VARCHAR2,
   mviewsite   IN   VARCHAR2,
   mview_id    IN   DATE | BINARY_INTEGER,
<
a name="997418">   flag        IN   BINARY_INTEGER,
   qry_txt     IN   VARCHAR2,
   re
p_type    IN   BINARY_INTEGER := DBMS_MVIEW.REG_UNKNOWN);



Parameters
 

Table 54-13 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 Oracle database version 8.x and higher master site or master materialized view s
ite. This name should not contain any double quotes.

<
a name="997446"> 
mview_id
 
The identi
fication number of the materialized view. Specify an Oracle database version 8.x and higher materialized view as a BINARY_INTEG
ER. Specify an Oracle database version 7 materialized view registering at an Oracle database version 8.x and higher master sit
es or master materialized view sites as a DATE.

 
flag
 
A cons
tant that describes the properties of the materialized view being registered. Valid constants that can be assigned include the follow
ing: 
DBMS_MVIEW.REG_ROWID_MVIEW for a rowid materialized view <
/a>

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.<
/td>

 
rep_type

 
Version of the materialized view. Valid constants that can be assig
ned include the following: 
DBMS_MVIEW.REG_V7_SNAPSHOT if the materialized view i
s at an Oracle database version 7 site 
DBMS_MVIEW.REG_V8_SNAPSHOT if the materia
lized view is at an Oracle database version 8.x or higher site 
DBMS_MVIEW.REG_UNKNOWN (the default) if you do not know whether the materialized view is at an Oracle database version 7 site or an Oracle database ve
rsion 8.x (or higher) site

 
Usage Notes
 
This procedure is invoked at the master site or mas
ter 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 value
s 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 ma
nually, the value of mview_id must be looked up in the materialized view data dictionary views by the person who calls t
he procedure.

 

UNREGISTER_MVIEW Procedure
 
This procedure enables the administration of individu
al 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 54-14 UNREGISTER_MVIEW Procedure Parameters



 Parameters
 Description


 
mviewowner
 
Owner of the materialized view.

 
mviewname
 
Name of the materialized view.

<
/a> 
mviewsite
 
Name of the materialized v
iew site.









Previous


Next


Copyrig
ht © 1998, 2003 Oracle Corporation

All Rights Reserved.





Home


Book List


Contents


Index


Master Index


Feedback

Parameter	Description
`tabowner< /code>`	Owner of the table being reorganized.
`tabname`	Name of the table being reorganized.

Parameter	Descript ion
`tabowner`	Owner of the table being reorga nized.
`tabn ame`	Name of the table being reorganized.

Parameter	Datatype	Description
`stmt_id`	`NUMBER`	Arbitrary string used to identify the statement in an `EXPLAIN PLAN`.
`select_clause`	`STRING`	The `SELECT statement to be analyzed.`
`num_rows`	`NUMBER`
`num_bytes`	`NUMBER`	Estimated number of bytes.

Parameter	< font face="Arial, Helvetica, sans-serif">Description
`mv`	The name of an existing materialized view (optionally qualified with the owner name separated by a ".") or a SELECT statement or a `CREATE` `MATERIALIZED` `VIEW` statement for a potential materialized view.
`stat ement_id`	A client-supplied unique identifier to associate out put 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 r ather than `MV_CAPABILITIES_TABLE`.

Parameter	Description
`query`	SQL `SELECT statement to be explained.`
`mv`	The fully qualified name of an exist ing 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 VA RRAY.

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
ESTIMATE_MVIEW_SIZE Procedure	Estimates the size of a materialized view t hat you might create, in bytes and rows
EXPLAIN_MVIEW Procedure	Explains what is possible with a materialized view or potential materialized view
EXPLAIN_REWRITE Procedures	Explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view
I_AM_A_REFRESH Function	Retur ns the value of the `I_AM_REFRESH` package state
PMARKER Function	Returns a partition marker from a rowid, and is used for Partition Change T racking (PCT)
PURGE_DIRECT_LOAD_LOG Procedure	Purges rows from the direct loader log after they are no longer needed by any materialized views (used wit h data warehousing)
PURGE_LOG Procedure	Purges rows from the materialized view log
PURGE_MVIEW_FROM_LOG Procedure	Purges rows from the materialized view log
RE FRESH Procedure	Refreshes one o r more materialized views that are not members of the same refresh group
REFRESH_ALL_MVIEWS Procedure	Refreshes all materialized views that do not refle ct changes to their master table or master materialized view
REFRESH_DEPENDENT Procedure	Refreshes all table-based materialized views that depend on a s pecified master table or master materialized view, or list of master tables or master materialized views
REGISTER_MVIEW P rocedure	Enables the administra tion of individual materialized views
UNREGISTER_MVIEW Procedure	Enables the administration of individual materialized views once invoked at a master si te or master materialized view site to unregister a materialized view

Para meter	Description
`mview_id`	If you want to execute this procedure based on the identification of the target materialized view, specify the materialized view i dentification using the `mview_id` parameter. Query the `DBA_BASE_TABLE_MVIEWS` view at the materialized view l og site for a listing of materialized view IDs. Executing this procedure based on the materia lized 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_RE GISTERED_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 u sing the `mviewname` parameter. Query the `DBA_REGISTERED_MVIEWS` view at the materialized view log site to vie w the materialized view names.
`mviewsite`	If you do not specify a `m view_id`, enter the site of the target materialized view using the `mviewsite` parameter. Query the `DBA_REGIST ERED_MVIEWS` view at the materialized view log site to view the materialized view sites.

Parameter	Description
`list \| tab`	Comma-delimited list of materialized views that you want to refresh. (Synonyms are not supported.) These materialized vie ws can be located in different schemas and have different master tables or master materialized views. However, all of the listed mate rialized 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 re fresh, and `A` or `a` indicates always refresh. `A` and `C` are equivalent. `P` or `p` refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables. < a name="997242"> If a materialized view does not have a corresponding refresh method (that is, if more material ized 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 s tatement 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`	< a name="997249"> Name of the materialized view site rollback segment to use while refreshing materialized views.
`push_deferre d_rpc`	Used by updatable materialized views only. Set this par ameter to `true` if you want to push changes from the materialized view to its associated master tables or master material ized views before refreshing the materialized view. Otherwise, these changes may appear to be temporarily lost.
`refresh_after_errors` < /td>	If this parameter is `true`, an updatable materialized view c ontinues to refresh even if there are outstanding conflicts logged in the `DEFERROR` view for the materialized view's mast er table or master materialized view. If this parameter is `true` and `atomic_refresh` is `false`, t his 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 i s 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 opti mal setting. Set purge to aggressive to trim the queue if multiple master replication groups are pushed to different target sites, an d updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently update d 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. < /td>
`heap_size`	Maximum number of transactions to be examined simultaneously for p arallel 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_refres h`	If this parameter is set to `true`, then the lis t of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point i n 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 transac tion.
`nest ed`	If `true`, then perform nested refresh operati ons for the specified set of materialized views. Nested refresh operations refresh all the depending materialized views and the speci fied set of materialized views based on a dependency order to ensure the nested materialized views are truly fresh with respect to th e underlying base tables.

Parameter	Description
`number_of_failures`	Returns the number of failures that occurred during processing.
`method`	< /a> 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 specifi ed, a materialized view is refreshed according to its default refresh method. `P` or `p` refreshes by recomputi ng the rows in the materialized view affected by changed partitions in the detail tables.
`rollback_seg`	Name of the materialized view site rollback segment to use while refreshing materialized views.
`refresh_after_err ors`	If this parameter is `true`, an updatable mate rialized view continues to refresh even if there are outstanding conflicts logged in the `DEFERROR` view for the materiali zed 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 t ime. 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.

Parameters	Description
`mviewowner`	Owner of the materialized view.
`mviewname`	Name of the materialized view.
< /a> `mviewsite`	Name of the materialized v iew site.

54 DBMS_MVIEW

Using DBMS_MVIEW

Operational Notes

Rules and Limits

Summary of DBMS_MVIEW Subprograms

Table 54-1 DBMS_MVIEW Package Subprograms

BEGIN_TABLE_REORGANIZATIO N Procedure

Syntax

Parameters

Table 54-2 BEGIN_TABLE_REORGANIZATION Procedure Param eters

END_TABLE_REORGANIZATION Procedure

Syntax

Parameters

Table 54-3 END_TABLE_ REORGANIZATION Procedure Parameters

EST IMATE_MVIEW_SIZE Procedure

Syntax

Parameters

Table 54-4 ESTIMATE_MVIEW_SIZE Procedure Parameters

EXPLAIN_MVIEW Procedure

Syntax

Parameters

Table 54-5 EXPLAIN_MVIEW Procedure Parameters

Usage Notes

EXPLAIN_REWRITE Procedures

Syntax

Parameters

Table 54-6 EXPLAIN_REWRITE Procedure Parameters

Usage Notes

I _AM_A_REFRESH Function

Syntax

Return Values

PMARKER Function

Syntax

Parameters

Table 54-7 PMARKER Procedure Parameters

PURGE_DIRECT_LOAD_LOG Procedure

Syntax

PURGE_LOG Procedure

Syntax

Parameters< /font>

Table 54- 8 PURGE_LOG Procedure Parameters

PURGE_ MVIEW_FROM_LOG Procedure

Syntax

Parameters

Table 54-9 PURGE_MVIEW_FROM_LOG Procedure Parameters

Usage Notes

REFRESH Procedure

Syntax

Parameters

Table 54-10 REFRESH Procedure Parameters

REFRESH_ALL_MVIEWS Procedure

Syntax

Parameters

Table 54-11 REFRESH_ALL_MVIEWS Procedure Parameters

REFRESH_DEPENDENT Procedure

Syntax

Parameters

Table 54-12 REFRESH_DEPENDENT Procedure Parameters

REGISTER_MVIEW Procedure

Syntax

Parameters

Table 54-13 REGISTER_MVIEW Procedure Parameters

Usage Notes

UNREGISTER_MVIEW Procedure

Syntax

Parameters

Table 54-14 UNREGISTER_MVIEW Procedure Parameters

54
DBMS_MVIEW