DBMS_SNAPSHOT, 2 of 2

DBMS_SNAPSHOT Package

Summary of Subprograms

Table 48-1 DBMS_SNAPSHOT Package Subprograms

Subprogram	Description
BEGIN_TABLE_REORGANIZATION Procedure	Performs a process to preserve snapshot data needed for refresh.
END_TABLE_REORGANIZATION Procedure procedure	Ensures that the snapshot data for the master table is valid and that the master table is in the proper state.
I_AM_A_REFRESH Function	Returns the value of the `I_AM_REFRESH` package state.
PURGE_DIRECT_LOAD_LOG Procedure	Purges rows from the direct loader log after they are no longer needed by any snapshots (used with data warehousing).
PURGE_LOG Procedure	Purges rows from the snapshot log.
PURGE_SNAPSHOT_FROM_LOG Procedure	Purges rows from the snapshot log.
REFRESH Procedure	Consistently refreshes one or more snapshots that are not members of the same refresh group.
REFRESH_ALL_MVIEWS Procedure	Refreshes all snapshots that do not reflect changes to their master table.
REFRESH_DEPENDENT Procedure	Refreshes all table-based snapshots that depend on a specified master table or list of master tables.
REGISTER_SNAPSHOT Procedure	Enables the administration of individual snapshots.
UNREGISTER_SNAPSHOT Procedure	Enables the administration of individual snapshots. Invoked at a master site to unregister a snapshot.

BEGIN_TABLE_REORGANIZATION Procedure

This procedure performs a process to preserve snapshot data needed for refresh. It must be called before a master table is reorganized.

See Also: The Oracle8i Replication Management API Reference for more information on reorganizing master tables that have snapshot logs.:

Syntax

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

Parameters

Table 48-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 must be called after a master table is reorganized. It ensures that the snapshot data for the master table is valid and that the master table is in the proper state.

See Also:
The Oracle8i Replication Management API Reference for more information on reorganizing master tables that have snapshot logs.

Syntax

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

Parameters

Table 48-3 END_TABLE_REORGANIZATION Procedure Parameters

Parameter	Description
tabowner	Owner of the table being reorganized.
tabname	Name of the table being reorganized.

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 snapshots 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_SNAPSHOT.I_AM_A_REFRESH()
  RETURN BOOLEAN;

Parameters

None

PURGE_DIRECT_LOAD_LOG Procedure

This procedure remove entries from the direct loader log after they are no longer needed for any known snapshot (materialized view). This procedure usually is used in environments using Oracle's data warehousing technology.

See Also:
Oracle8i Data Warehousing Guide for more information.

Syntax

DBMS_SNAPSHOT.PURGE_DIRECT_LOAD_LOG();

PURGE_LOG Procedure

This procedure purges rows from the snapshot log.

Syntax

DBMS_SNAPSHOT.PURGE_LOG (
   master        IN   VARCHAR2,
   num           IN   BINARY_INTEGER := 1,
   flag          IN   VARCHAR2       := 'NOP');

Parameters

Table 48-4 PURGE_LOG Procedure Parameters

Parameter Description

Parameter	Description
master	Name of the master table.
num	Number of least recently refreshed snapshots whose rows you want to remove from snapshot log. For example, the following statement deletes rows needed to refresh the two least recently refreshed snapshots: `dbms_snapshot.purge_log('master_table', 2);` To delete all rows in the snapshot log, indicate a high number of snapshots to disregard, as in this example: `dbms_snapshot.purge_log('master_table',9999);` This statement completely purges the snapshot log that corresponds to `MASTER_TABLE` if fewer than 9999 snapshots are based on `MASTER_TABLE`. A simple snapshot whose rows have been purged from the snapshot log must be completely refreshed the next time it is refreshed.
flag	Specify `DELETE` to guarantee that rows are deleted from the snapshot log for at least one snapshot. This argument can override the setting for the argument `num`. For example, the following statement deletes rows from the least recently refreshed snapshot that actually has dependent rows in the snapshot log: `dbms_snapshot.purge_log('master_table',1,'DELETE');`

master

Name of the master table.

num

Number of least recently refreshed snapshots whose rows you want to remove from snapshot log. For example, the following statement deletes rows needed to refresh the two least recently refreshed snapshots:

dbms_snapshot.purge_log('master_table', 2);

To delete all rows in the snapshot log, indicate a high number of snapshots to disregard, as in this example:

dbms_snapshot.purge_log('master_table',9999);

This statement completely purges the snapshot log that corresponds to MASTER_TABLE if fewer than 9999 snapshots are based on MASTER_TABLE. A simple snapshot whose rows have been purged from the snapshot log must be completely refreshed the next time it is refreshed.

flag

Specify DELETE to guarantee that rows are deleted from the snapshot log for at least one snapshot. This argument can override the setting for the argument num. For example, the following statement deletes rows from the least recently refreshed snapshot that actually has dependent rows in the snapshot log:

dbms_snapshot.purge_log('master_table',1,'DELETE');

PURGE_SNAPSHOT_FROM_LOG Procedure

This procedure is called on the master site to delete the rows in snapshot refresh related data dictionary tables maintained at the master site for the specified snapshot identified by its snapshot_id or the combination of the snapowner, snapname, and the snapsite. If the snapshot specified is the oldest snapshot to have refreshed from any of the master tables, then the snapshot log is also purged. This procedure does not unregister the snapshot.

In case there is an error while purging one of the snapshot logs, the successful purge operations of the previous snapshot logs are not rolled back. This is to minimize the size of the snapshot logs. In case of an error, this procedure can be invoked again until all the snapshot logs are purged.

Syntax

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   snapshot_id   IN   BINARY_INTEGER  |
   snapowner     IN   VARCHAR2,
   snapname      IN   VARCHAR2, 
   snapsite      IN   VARCHAR2);

Note:
This procedure is overloaded. The snapshot_id parameter is mutually exclusive with the three remaining parameters: snapowner, snapname, and snapsite.

Parameters

Table 48-5 PURGE_SNAPSHOT_FROM_LOG Procedure Parameters

Parameter	Description
snapshot_id	If you want to execute this procedure based on the ID of the target snapshot, specify the snapshot ID using the `snapshot_id` parameter. Query the `DBA_SNAPSHOT_LOGS` view at the snapshot log site for a listing of snapshot IDs. Executing this procedure based on the snapshot ID is useful if the target snapshot is not listed in the list of registered snapshots (`DBA_REGISTERED_SNAPSHOTS`).
snapowner	If do not specify a `snapshot_id`, enter the owner of the target snapshot using the `snapowner` parameter. Query the `DBA_REGISTERED_SNAPSHOTS` view at the snapshot log site to view the snapshot owners.
snapname	If do not specify a `snapshot_id`, enter the name of the target snapshot using the `snapname` parameter. Query the `DBA_REGISTERED_SNAPSHOTS` view at the snapshot log site to view the snapshot names.
snapsite	If do not specify a `snapshot_id`, enter the site of the target snapshot using the `snapsite` parameter. Query the `DBA_REGISTERED_SNAPSHOTS` view at the snapshot log site to view the snapshot sites.

REFRESH Procedure

This procedure refreshes a list of snapshots.

Syntax

DBMS_SNAPSHOT.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 48-6 REFRESH Procedure Parameters (Page 1 of 2)

Parameter	Description
list \| tab	Comma-separated list of snapshots that you want to refresh. (Synonyms are not supported.) These snapshots can be located in different schemas and have different master tables. However, all of the listed 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 snapshot.
method	A string of refresh methods indicating how to refresh the listed snapshots. F or f indicates fast refresh, ? indicates force refresh, C or c indicates complete refresh, and A or a indicates always refresh, If a snapshot does not have a corresponding refresh method (that is, if more snapshots are specified than refresh methods), then that snapshot is refreshed according to its default refresh method. For example, the following EXECUTE statement within SQL*Plus: dbms_snapshot.refresh ('s_emp,s_dept,scott.s_salary','CF'); performs a complete refresh of the S_EMP snapshot, a fast refresh of the S_DEPT snapshot, and a default refresh of the SCOTT.S_SALARY snapshot.
rollback_seg	Name of the snapshot site rollback segment to use while refreshing snapshots.
push_deferred_rpc	Used by updatable snapshots only. Set this parameter to TRUE if you want to push changes from the snapshot to its associated master before refreshing the snapshot. Otherwise, these changes may appear to be temporarily lost.
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.
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, set this parameter to 0 and occasionally execute PUSH with this parameter set to 2 to reduce the queue.
parallelism	0 means serial propagation, n > 1 means parallel propagation with n parallel server processes, and 1 means parallel propagation using only one parallel server process.
heap_size	Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set this parameter unless directed to do so by Oracle Worldwide Support.
atomic_refresh	If this parameter is set to TRUE, then the list of snapshots is 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 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.

REFRESH_ALL_MVIEWS Procedure

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

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_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 48-7 REFRESH_ALL_MVIEWS Procedure Parameters (Page 1 of 2)

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 snapshot 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. If no method is specified, a snapshot is refreshed according to its default refresh method.
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.

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 48-8 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.

REGISTER_SNAPSHOT Procedure

This procedure enables the administration of individual snapshots.

Syntax

DBMS_SNAPSHOT.REGISTER_SNAPSHOT (
   snapowner   IN   VARCHAR2,
   snapname    IN   VARCHAR2,
   snapsite    IN   VARCHAR2,
   {snapshot_id IN   DATE | BINARY_INTEGER,
   flag        IN   BINARY_INTEGER,}
   qry_txt     IN   VARCHAR2,
   rep_type    IN   BINARY_INTEGER := DBMS_SNAPSHOT.REG_UNKNOWN);

Note:
This procedure is overloaded. The snapshot_id and flag parameters are mutually exclusive.

Parameters

Table 48-9 REGISTER_SNAPSHOT Procedure Parameters

Parameter	Description
sowner	Owner of the snapshot.
snapname	Name of the snapshot.
snapsite	Name of the snapshot site for a snapshot registering at an Oracle8 or greater master. This name should not contain any double quotes.
snapshot_id	The identification number of the snapshot. Specify a version 8 snapshot as a `BINARY_INTEGER`. Specify a version 7 snapshot registering at an version 8 or greater master sites as a `DATE`.
flag	PL/SQL package variable indicating whether subsequent CREATE or MOVE statements are registered in the query text.
query_txt	The first 32,000 bytes of the snapshot definition query.
rep_type	Version of the snapshot. Valid constants that can be assigned include `reg_unknown` (the default), `reg_v7_group`, `reg_v8_group`, and `reg_repapi_group`.

Usage Notes

This procedure is invoked at the master site by a remote snapshot site using a remote procedure call. If REGISTER_SNAPSHOT is called multiple times with the same SNAPOWNER, SNAPNAME, and SNAPSITE, then the most recent values for SNAPSHOT_ID, FLAG, and QUERY_TXT are stored. If a query exceeds the maximum VARCHAR2 size, then QUERY_TXT contains the first 32000 characters of the query and the remainder is truncated. When invoked manually, the values of SNAPSHOT_ID and FLAG have to be looked up in the snapshot views by the person who calls the procedure.

If you do not want the snapshot query registered at the master site, then call the SET_REGISTER_QUERY_TEXT procedure with the option set to FALSE. To see the most recent setting of the option, call the GET_REG_QUERY_TEXT_FLAG function at the snapshot site before issuing the DDL.

UNREGISTER_SNAPSHOT Procedure

This procedure enables the administration of individual snapshots. It is invoked at a master site to unregister a snapshot.

Syntax

DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (
   snapowner      IN   VARCHAR2,
   snapname       IN   VARCHAR2,
   snapsite       IN   VARCHAR2);

Parameters

Table 48-10 UNREGISTER_SNAPSHOT Procedure Parameters

Parameters	Description
snapowner	Owner of the snapshot.
snapname	Name of the snapshot.
snapsite	Name of the snapshot site.