Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 14.2 DBMS_REFRESH: Managing Snapshot GroupsChapter 14
Snapshots
Next: 14.4 DBMS_REPCAT: Managing Snapshot Replication Groups
 

14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation

The 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_SNAPSHOT

The 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 programs

Table 14.4 summarizes the programs available through DBMS_OFFLINE_SNAPSHOT.


Table 14.4: DBMS_OFFLINE_SNAPSHOT Programs

Name

Description

Use in

SQL?

BEGIN_LOAD

Call before beginning to load data from an export file

No

END_LOAD

Call after the load is complete

No

14.3.1.2 DBMS_OFFLINE_SNAPSHOT exceptions

The DBMS_OFFLINE_SNAPSHOT package raises the following exceptions:

Name

Number

Description

badargument

-23430

The gname, sname, master_site, or snapshot_oname parameter is NULL or".

missingremotesnap

-23361

The snapshot_oname parameter does not exist at the remote master site (master_site parameter).

snaptabmismatch

-23363

The base table name of the snapshot at master site and snapshot site do not match.

14.3.2 DBMS_OFFLINE_SNAPSHOT Interface

This section describes the programs available through the DBMS_OFFLINE_SNAPSHOT package.

14.3.2.1 The DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD procedure

Call 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.

Name

Description

gname

The replication group to which the new snapshot belongs.

sname

The schema that owns the new snapshot.

master_site

The global name of the snapshot master site.

snapshot_oname

The name of the temporary snapshot created at the master site.

storage_c

Optional storage clause for the new snapshot.

comment

Optional 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 procedure

Call 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.


Previous: 14.2 DBMS_REFRESH: Managing Snapshot GroupsOracle Built-in PackagesNext: 14.4 DBMS_REPCAT: Managing Snapshot Replication Groups
14.2 DBMS_REFRESH: Managing Snapshot GroupsBook Index14.4 DBMS_REPCAT: Managing Snapshot Replication Groups

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.