Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

45
DBMS_SNAPSHOT

DBMS_SNAPSHOT enables you to refresh snapshots that are not part of the same refresh group and purge logs.

Note:

DBMS_MVIEW is a synonym for DBMS_SNAPSHOT. This synonym may be used in the future with data warehousing.  

Summary of Subprograms

Table 45-1 DBMS_SNAPSHOT Package Subprograms
Subprogram  Description  
BEGIN_TABLE_REORGANIZATION 
procedure
 

Performs a process to preserve snapshot data needed for refresh.  

 
END_TABLE_REORGANIZATION
 

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
 

Refreshes a list of snapshots.  

 
REFRESH_ALL_MVIEWS 
procedure
 

Refreshes all snapshots that have not been refreshed because the most recent bulk load to a dependent detail table.  

 
REFRESH_DEPENDENT procedure
 

Refreshes all table-based snapshots that depend on a specified detail table or list of detail 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:

See "Administering a Replicated Environment" in the Oracle8i Replication manual.  

Syntax

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

Parameters

Table 45-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

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:

See "Administering a Replicated Environment" in the Oracle8i Replication manual.  

Syntax

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

Parameters

Table 45-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.

Syntax

DBMS_SNAPSHOT.I_AM_A_REFRESH 
  RETURN BOOLEAN;

Parameters

None

Returns

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.

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 will usually be used in environments using Oracle's Data Warehousing technology.

See Also:

For more information, see Oracle8i Tuning.  

Syntax

DBMS_SNAPSHOT.PURGE_DIRECT_LOAD_LOG;

Parameters

None.

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 45-4 PURGE_LOG Procedure Parameters
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');
 

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);

Parameters

Table 45-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, then 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).

If you specify a snapshot ID, then do not specify values for the snapowner, snapname, or snapsite parameters.  

 
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, then 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);

Parameters

Table 45-6 REFRESH Procedure Parameters
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 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 is TRUE, then an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this is TRUE and if atomic_refresh is FALSE, then this procedure will continue 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), then 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, then set purge to do not purge and occasionally execute PUSH with purge set to aggressive to reduce the queue.  

 
parallelism
 

0 means serial propagation, n > 0 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 is set to TRUE, then the list of snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, then none of the snapshots will be updated.

If this is set to FALSE, then each of the snapshots will be 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 if 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:

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 45-7 REFRESH_ALL_MVIEWS Procedure Parameters
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, then 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 is TRUE, then an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this is TRUE, and if atomic_refresh is FALSE, then this procedure continues to refresh other snapshots if it fails while refreshing a snapshot.  

 
atomic_refresh
 

If this is set to TRUE, then the refreshed snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, then none of the snapshots will be updated.

If this is set to FALSE, then each of the refreshed snapshots will be 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, if and the Summary Management option is not purchased, then an error is raised.  

REFRESH_DEPENDENT procedure

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

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);

Parameters

Table 45-8 REFRESH_DEPENDENT Procedure Parameters
Parameter  Description  
number_of_failures
 

Returns the number of failures that occurred during processing.  

 
list
tab
 

Comma-separated list of detail 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 is TRUE, then an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this is TRUE, and if atomic_refresh is FALSE, then this procedure will continue to refresh other snapshots if it fails while refreshing a snapshot.  

 
atomic_refresh
 

If this is set to TRUE, then the refreshed snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, then none of the snapshots will be updated.

If this is set to FALSE, then each of the refreshed snapshots will be refreshed in separate transactions. The number of job queue processes must be set to 1 or greater if this parameter is FALSE.

If FALSE, if 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 45-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 should not contain any double quotes.  

 
snapshot_id
 

The identification number of the snapshot. Specify an Oracle8 snapshot as a BINARY_INTEGER; specify an Oracle7 snapshot registering at an Oracle8 or greater master sites as a DATE.  

 
flag
 

PL/SQL package variable indicating whether subsequent create or move commands are registered in the query text.  

 
query_txt
 

The first 32,000 bytes of the query.  

 
rep_type
 

Version of the snapshot. Valid constants that can be assigned include reg_uknown (the default), reg_v7_group, reg_v8_group, and reg_repapi_group.  

Usage Notes

This procedure is executed at the master site, and can be done by 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. 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 45-10 UNREGISTER_SNAPSHOT Procedure Parameters
Parameters  Description  
snapowner
 

Owner of the snapshot.  

 
snapname
 

Name of the snapshot.  

 
snapsite
 

Name of the snapshot site.  


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index