[Chapter 14] 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot InstantiationChapter 14Snapshots 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot InstantiationThe DBMS_OFFLINE_SNAPSHOT package allows you to instantiate snapshots without having to run the CREATE SNAPSHOT command or the DBMS_REPEAT.SNAPSHOT_REPOBJECT procedure over the network (those methods are described under DBMS_REPCAT, later in this chapter). Doing offline instantiation in this way is particularly useful in cases where you wish to instantiate a snapshot site with a large amount of data in an advanced replication environment. Offline instantiation refers to the population of snapshots with the import and export utilities, as opposed to using the DBMS_SNAPSHOT.REFRESH procedure. This technique is less time-consuming and less taxing on your network, and it minimizes the time your environment must be quiesced. You will typically use DBMS_OFFLINE_SNAPSHOT's BEGIN_LOAD and END_LOAD procedures in conjunction with the DBMS_REPCAT package's CREATE_SNAPSHOT_REPGROUP procedure; this procedure creates a new replicated snapshot group. The following sections summarize the syntax of the calls to BEGIN_LOAD and END_LOAD. See the later section Section 14.4, "DBMS_REPCAT: Managing Snapshot Replication Groups"" for a discussion of the DBMS_REPCAT procedure and how these procedures work together to instantiate snapshots in an advanced replication environment.14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOTThe DBMS_OFFLINE_SNAPSHOT package is created when the Oracle database is installed. The dbmsofln.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped sql script prvtofln.plb creates the public synonym DBMS_OFFLINE_SNAPSHOT. No EXECUTE privileges are granted on DBMS_OFFLINE_SNAPSHOT; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.14.3.1.1 DBMS_OFFLINE_SNAPSHOT programsTable 14.4 summarizes the programs available through DBMS_OFFLINE_SNAPSHOT.Table 14.4: DBMS_OFFLINE_SNAPSHOT ProgramsNameDescriptionUse in SQL?BEGIN_LOADCall before beginning to load data from an export fileNoEND_LOADCall after the load is completeNo14.3.1.2 DBMS_OFFLINE_SNAPSHOT exceptionsThe DBMS_OFFLINE_SNAPSHOT package raises the following exceptions: NameNumberDescriptionbadargument-23430The gname, sname, master_site, or snapshot_oname parameter is NULL or".missingremotesnap-23361The snapshot_oname parameter does not exist at the remote master site (master_site parameter).snaptabmismatch-23363The base table name of the snapshot at master site and snapshot site do not match.14.3.2 DBMS_OFFLINE_SNAPSHOT InterfaceThis section describes the programs available through the DBMS_OFFLINE_SNAPSHOT package.14.3.2.1 The DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD procedureCall the BEGIN_LOAD procedure before beginning to load data from an export file. The specifications for the Oracle7 and Oracle8 versions differ as follows.Here is the Oracle7 specification:PROCEDURE DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (gname IN VARCHAR2, sname IN VARCHAR2, master_site IN VARCHAR2, snapshot_oname IN VARCHAR2, storage_c IN VARCHAR2 := '', comment IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (gname IN VARCHAR2, sname IN VARCHAR2, master_site IN VARCHAR2, snapshot_oname IN VARCHAR2, storage_c IN VARCHAR2 := '', comment IN VARCHAR2 := '', min_communicatio IN BOOLEAN := TRUE );Parameters are summarized in the following table.NameDescriptiongnameThe replication group to which the new snapshot belongs.snameThe schema that owns the new snapshot.master_siteThe global name of the snapshot master site.snapshot_onameThe name of the temporary snapshot created at the master site.storage_cOptional storage clause for the new snapshot.commentOptional comment for the snapshot; stored with entry in DBA_SNAPSHOTS if supplied.min_communication(Oracle8 only)The min_communication parameter controls how the update trigger on updateable snapshots queues changes back to the master site. If this parameter is set to TRUE (the default), then old column values are sent only if the update changes their value. New column values are sent only if the column is part of primary key, or if the column is in a column group that has been modified.The BEGIN_LOAD procedure does not raise any exceptions.14.3.2.2 The DBMS_OFFLINE_SNAPSHOT.END_LOAD procedureCall the END_LOAD procedure after the data import (initiated by the BEGIN_LOAD procedure) is complete. The specification is the same for Oracle7 and Oracle8:PROCEDURE DBMS_OFFLINE_SNAPSHOT.END_LOAD (gname IN VARCHAR2, sname IN VARCHAR2, snapshot_oname IN VARCHAR2);Parameters have the same meanings as for the BEGIN_LOAD procedure (see the previous section). The END_LOAD procedure does not raise any exceptions.14.2 DBMS_REFRESH: Managing Snapshot Groups14.4 DBMS_REPCAT: Managing Snapshot Replication Groups Copyright (c) 2000 O'Reilly & Associates. All rights reserved.