REFRESH_DEPENDENT procedure

This procedure refreshes all snapshots (materialized views) with the following properties:

• The snapshot depends on a master table in the list of specified master tables.

• The snapshot has not been refreshed since the most recent change to a master table on which it depends.

• The snapshot and all of the master tables on which it depends are local.

• The snapshot is in the view DBA_MVIEWS.

This procedure is intended for use with data warehouses.

Syntax

DBMS_SNAPSHOT.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 8-312 REFRESH_DEPENDENT Procedure Parameters (Page 1 of 2)

Parameter	Description
number_of_failures	Returns the number of failures that occurred during processing.
list | tab	Comma-separated list of master tables on which snapshots can depend. (Synonyms are not supported.) These tables and the snapshots that depend on them can be located in different schemas. However, all of the tables and snapshots must be in your local database. Alternatively, you may pass in a PL/SQL 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 snapshots. All of the snapshots 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. If a table does not have a corresponding refresh method (that is, if more tables are specified than refresh methods), then any snapshot that depends on that table is refreshed according to its default refresh method. For example, the following EXECUTE statement within SQL*Plus: dbms_snapshot.refresh_dependent ('emp,dept,scott.salary','CF'); performs a complete refresh of the snapshots that depend on the EMP table, a fast refresh of the snapshots that depend on the DEPT table, and a default refresh of the snapshots that depend on the SCOTT.SALARY table.
rollback_seg	Name of the snapshot site rollback segment to use while refreshing snapshots.
refresh_after_errors	If this parameter is TRUE, an updatable snapshot continues to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this parameter is TRUE and atomic_refresh is FALSE, this procedure continues to refresh other snapshots if it fails while refreshing a snapshot.
atomic_refresh	If this parameter is set to TRUE, then the refreshed snapshots are refreshed in a single transaction. All of the refreshed snapshots are updated to a single point in time. If the refresh fails for any of the snapshots, none of the snapshots are updated. If this parameter is set to FALSE, then each of the refreshed snapshots is refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is FALSE. If FALSE and the Summary Management option is not purchased, then an error is raised.