home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: IV. Distributed Database Packages Chapter 14 Next: 14.2 DBMS_REFRESH: Managing Snapshot Groups
 

14. Snapshots

Oracle provides a number of packages that let you perform various types of administrative operations on snapshots and snapshot logs. Most of these administrative operations are relevant only if you are using snapshot groups or the Oracle advanced replication option. This chapter describes the following packages:

DBMS_SNAPSHOT

Lets you maintain snapshots and snapshot logs.

DBMS_OFFLINE_SNAPSHOT

Allows you to instantiate snapshots without having to run the CREATE SNAPSHOT command over the network. This package is particularly useful if you need to instantiate extremely large snapshots.

DBMS_REFRESH

Administers snapshot groups at a snapshot site.

DBMS_REPCAT

Performs a number of advanced replication operations. This chapter describes only the DBMS_REPCAT programs that deal with snapshots; all other programs are described in Chapter 15, Advanced Replication .

Even if you are using PL/SQL's built-in snapshot packages, you will continue to use the CREATE SNAPSHOT command to create your snapshots.

14.1 DBMS_SNAPSHOT: Managing Snapshots

The DBMS_SNAPSHOT package contains programs that allow you to maintain snapshots and snapshot logs, and to set and query package state variables associated with the advanced replication option.

14.1.1 Getting Started with DBMS_SNAPSHOT

The DBMS_SNAPSHOT package is created when the Oracle database is installed. The dbmssnap.sql script (found in the built-in packages source directory, as described in Chapter 1, Introduction ) contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_SNAPSHOT for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

Table 14-1 lists the programs contained in the DBMS_SNAPSHOT package.


Table 14.1: DBMS_SNAPSHOT Packages

Name

Description

Use in SQL?

BEGIN_TABLE_REORGANIZATION (Oracle8 only)

Called prior to reorganizing a master table (e.g., through export/import); saves data required to refresh snapshots

No

END_TABLE_REORGANIZATION (Oracle8 only)

Called after reorganizing a master table (e.g., through export/import); validates data required to refresh snapshots

No

I_AM_A_REFRESH

Returns value of REP$WHAT_AM_I.I_AM_A_SNAPSHOT

No

PURGE_LOG

Purges snapshot log

No

REFRESH

Refreshes a snapshot

No

REGISTER_SNAPSHOT

(Oracle8 only)

Records information about snapshots at the master site in the DBA_REGISTERED_SNAPSHOTS data dictionary view

No

SET_I_AM_A_REFRESH

Sets REP$WHAT_AM_I.I_AM_A_SNAPSHOT to specified value

No

UNREGISTER_SNAPSHOT

(Oracle8 only)

Removes information about snapshots at the master site from the DBA_REGISTERED_SNAPSHOTS data dictionary view

No

DBMS_SNAPSHOT does not define any exceptions.

NOTE: All of the programs in DBMS_SNAPSHOT are available regardless of whether you are using snapshot groups or the advanced replication option.

14.1.2 Using the I_AM_A_REFRESH Package State Variable

The I_AM_A_REFRESH and SET_I_AM_A_REFRESH programs query and set Oracle's REP$I_AM_A_REFRESH package variable. Oracle uses this variable in replication triggers and elsewhere internally to determine whether a given DML statement should be replicated to other master sites.

14.1.2.1 The DBMS_SNAPSHOT.I_AM_A_REFRESH function

The I_AM_A_REFRESH function queries the REP$I_AM_A_REFRESH package variable. If this variable equals "Y," then the session is refreshing a snapshot or applying propagated DML to a replicated table. The header for the function follows:

FUNCTION DBMS_SNAPSHOT.I_AM_A_REFRESH RETURN BOOLEAN;

The function does not raise any exceptions.

14.1.2.1.1 Examples

Let's look at several examples of querying the I_AM_A_REFRESH package variable.

14.1.2.1.2 Generating replication support

Suppose now that you are replicating a table named COUNTRIES in the SPROCKET schema:

SQL> DESC sprocket.countries
Name               Null?        Type
---------------    ---------    --------------
COUNTRY_ID         NOT NULL     NUMBER(6)
ISO3166_NUMBER     NOT NULL     NUMBER(3)
ISO3166_NAME       NOT NULL     VARCHAR2(50)
ISO2_CODE          NOT NULL     VARCHAR2(2)
ISO3_CODE          NOT NULL     VARCHAR2(3)
AUDIT_DATE         NOT NULL     DATE
AUDIT_USER         NOT NULL     VARCHAR2(30)
GLOBAL_NAME        NOT NULL     VARCHAR2(20)

When you generate replication support for this table with DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (described in Chapter 15 ), Oracle creates an AFTER ROW trigger named COUNTRIES$RT, which queues DML to other master sites. The text of the trigger follows:

    after delete or insert or update on "SPROCKET"."COUNTRIES"
  for each row
declare
  flag char;
begin
  if "COUNTRIES$TP".active then
    if inserting then
      flag := 'I';
    elsif updating then
      flag := 'U';
    elsif deleting then
      flag := 'D';
    end if;
    "COUNTRIES$TP".replicate(
      :old."AUDIT_DATE",:new."AUDIT_DATE",
      :old."AUDIT_USER",:new."AUDIT_USER",
      :old."COUNTRY_ID",:new."COUNTRY_ID",
      :old."GLOBAL_NAME",:new."GLOBAL_NAME",
      :old."ISO2_CODE",:new."ISO2_CODE",
      :old."ISO3166_NAME",:new."ISO3166_NAME",
      :old."ISO3166_NUMBER",:new."ISO3166_NUMBER",
      :old."ISO3_CODE",:new."ISO3_CODE",
      flag);
  end if;
end;

As you can see, this trigger replicates DML only if the function COUNTRIES$TP.active is TRUE. This ACTIVE function uses DBMS_SNAPSHOT.I_AM_A_REFRESH as follows:

function active return boolean
is
begin
return (not((is_snapshot and dbms_snapshot.I_am_a_refresh) or
not dbms_reputil.replication_is_on));
end active;

Oracle uses the active function, which calls DBMS_SNAPSHOT.I_AM_A_REFRESH, to distinguish between your application's DML operations and the DML that is being propagated from another master site.

The base table of an updateable snapshot has a trigger that also uses the I_AM_A_REFRESH function.

14.1.2.1.3 Auditing triggers

Under some circumstances, you may need to determine the source of DML statements. For example, you will notice that the countries table has a number of fields used for auditing: audit_date, audit_user, and global_name. We have a BEFORE ROW trigger that populates these fields.

CREATE OR REPLACE TRIGGER countries_audit
BEFORE INSERT OR UPDATE ON countries
FOR EACH ROW

DECLARE
        vGlobalName     VARCHAR2(30) := DBMS_REPUTIL.GLOBAL_NAME;
BEGIN
        IF NOT (DBMS_SNAPSHOT.I_AM_A_REFRESH) THEN
        BEGIN 
                :new.audit_date         := SYSDATE;
                :new.audit_user         := USER;
                :new.global_name      := vGlobalName;
        END IF;

END;

This trigger fires when an application performs an INSERT or UPDATE, but not when the DML is propagated from other sites.

NOTE: All row-level replication triggers are AFTER ROW triggers. Although a table can have multiple triggers of the same type, you cannot control the order in which they are fired. Therefore, it is safest to use BEFORE ROW triggers to perform auditing on replicated tables; in this way, you are guaranteed that BEFORE ROW triggers fire before AFTER ROW triggers.

14.1.2.2 The DBMS_SNAPSHOT.SET_I_AM_A_REFRESH procedure

The SET_I_AM_A_REFRESH procedure sets the I_AM_A_REFRESH package variable. The header for the procedure is:

PROCEDURE DBMS_SNAPSHOT.SET_I_AM_A_REFRESH  (value IN  BOOLEAN);

where value is the value (Y or N) being set. This procedure does not raise any exceptions.

14.1.2.2.1 Example

If you need to enable and disable replication triggers at the session level, you can do so with the SET_I_AM_A_REFRESH procedure. To enable the triggers, specify the following:

DBMS_SNAPSHOT.SET_I_AM_A_REFRESH( value => FALSE )

To disable them, specify the following:

DBMS_SNAPSHOT.SET_I_AM_A_REFRESH( value => TRUE )

Use this package carefully, because disabling replication triggers effectively disables any conflict resolution mechanisms you may have defined. (See Chapter 17, Deferred Transactions and Remote Procedure Calls , for a discussion of these mechanisms.)

14.1.3 Refreshing Snapshots

Calling the REFRESH procedure from a snapshot site forces the refresh of the specified snapshot(s). Typically, this procedure is used to refresh an individual snapshot, or a group of snapshots that are not in the same snapshot refresh group.

14.1.3.1 The DBMS_SNAPSHOT.REFRESH procedure

Call the REFRESH procedure to force a snapshot refresh. The specifications for the Oracle7 and Oracle8 versions of the REFRESH procedure differ. Note that the Version 8.0 implementation adds parameters that support parallelism, and drops the execute_as_user parameter. Both versions are overloaded, allowing you to specify the list of snapshots as a comma-delimited string in the list parameter, or as a PL/SQL table in the tab parameter. The other parameters are identical for the two versions.

Here is the Oracle7 specification:

PROCEDURE DBMS_SNAPSHOT.REFRESH
   (list IN VARCHAR2,
    method IN VARCHAR2 DEFAULT NULL,
    rollback_seg IN VARCHAR2 DEFAULT NULL,
    push_deferred_rpc IN BOOLEAN DEFAULT TRUE,
    refresh_after_errors IN BOOLEAN DEFAULT FALSE,
    execute_as_user IN BOOLEAN DEFAULT FALSE );

PROCEDURE DBMS_SNAPSHOT.REFRESH
   (tab IN OUT dbms_utility.uncl_array,
    method  IN VARCHAR2 DEFAULT NULL,
    rollback_seg IN VARCHAR2 DEFAULT NULL,
    push_deferred_rpc IN BOOLEAN DEFAULT TRUE,
    refresh_after_errors IN BOOLEAN DEFAULT FALSE,
    execute_as_user IN BOOLEAN DEFAULT FALSE );

Here is the Oracle8 specification:

PROCEDURE DBMS_SNAPSHOT.REFRESH
   (list IN VARCHAR2,
    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);
 
  PROCEDURE DBMS_SNAPSHOT.REFRESH
    (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);

Parameters are summarized in the following table.

Name

Description

list

Comma-separated list of snapshots to be refreshed. Use list or tab.

tab

PL.SQL table of snapshots to be refreshed. Use list or tab.

method

Refresh method:

`?' uses the default refresh method. If you specified a refresh method when you created the snapshot, that is the default method. Otherwise, Oracle uses a fast refresh if possible, and a complete refresh if not.

`F' or `f' uses fast refresh if possible, and returns ORA-12004 if not.

`C' or `c' uses a COMPLETE refresh. This parameter should include a single character for each snapshot specified in list or tab, in the same order as the snapshot names appear. If list or tab contains more snapshots than the method list, the additional snapshots are refreshed with their default method.

rollback_seg

Optional; specifies the rollback segment to use for the refresh.

push_deferred_rpc

Optional; for updateable snapshots only. If TRUE (the default), then local updates are sent back to the master site before the snapshot is refreshed (otherwise, local updates will be temporarily overwritten).

refresh_after_errors

Optional; for updateable snapshots only. If TRUE, proceed with the refresh even if outstanding errors (conflicts) are logged in the DEFERROR data dictionary view at the master site. Default is FALSE.

execute_as_user

(Version 7 only)

If FALSE (the default) then the call to the remote system is performed under the privilege domain of the user that created the snapshot. If TRUE, the call is performed as the user calling the refresh procedure.

purge_option

(Oracle8 only)

If push_deferred_rpc is TRUE, this designates the purge method; default is 1.

  • 0 No purge

  • 1 Lazy purge (optimized for time)

  • 2 Aggressive purge (complete)

parallelism

(Oracle8 only)

If push_defered_rpc is TRUE, this determines the maximum degree of parallelism; default is 1.

  • 0 Serial

  • 1 Parallel with one slave

  • N Parallel with N slaves (N > 1)

heap_size

(Oracle8 only)

Used only if parallelism > 0. Sets the maximum number of transactions to be examined simultaneously for determining parallel scheduling. Oracle determines this value internally; you are advised not to use it.

The REFRESH procedure does not raise any exceptions.

All of the snapshots passed to list or tab are refreshed as a single transaction; all or none are refreshed. In addition, the refreshed snapshots will respect all integrity constraints that exist among the master tables.

You might want to force a manual refresh of a snapshot if the next scheduled refresh is too far in the future, or if you have repaired a problem that caused the scheduled refresh job to break. Forcing a manual refresh of a snapshot does not alter its refresh schedule.

A FAST refresh requires a snapshot log on the master table, and is possible only for simple snapshots in Oracle7; Oracle8 supports fast refreshes subquery snapshots meeting certain conditions. Fast refreshes read the snapshot log to determine which rows have changed since the last refresh, and only those rows are updated.

If you are concerned about the amount of rollback the refresh will require, you can use the rollback_seg parameter to designate a rollback segment that is suitably sized for the transaction. However, you are not guaranteed that no other transactions will use this rollback segment. In general, you should consider making relatively large rollback segments if you anticipate frequent refreshes of large snapshots.

14.1.3.1.1 Restrictions

You can call REFRESH only from a snapshot site.

14.1.3.1.2 Examples

Once you are familiar with the various parameters to the REFRESH procedure, it becomes simple to use, as the following examples illustrate.

14.1.3.1.3 Read-only snapshot

This example shows a refresh as a read-only snapshot named PRICE_LIST:

BEGIN
	DBMS_SNAPSHOT.REFRESH (list => 'PRICES');
END;

This is the simplest possible refresh method. Note that since we have not provided a schema name, this would have to be executed from the snapshot owner's account.

14.1.3.1.4 Related read-only snapshots

In the next example, we refresh a set of related read-only snapshots.

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
	vSnapshotList(1) = 'COUNTRIES'
	vSnapshotList(2) = 'STATES'
	vSnapshotList(3) = 'POSTAL_CODES'
	vSnapshotList(4) = 'CUSTOMER_ADDRESSES'

	DBMS_SNAPSHOT.REFRESH(	tab => vSnapShotList,
				method => 'CCF?',
				rollback_segment => 'RB1'
				execute_as_user => FALSE);
END;

This example illustrates several points:

  1. You can provide the list of snapshots as a PL/SQL table. Oracle will refresh all of the snapshots in one atomic transaction; either all or none of the snapshots are refreshed. All referential consistencies among the master tables will be preserved in the snapshot tables.

  2. You can specify different refresh methods for each snapshot. This example performs a complete refresh on COUNTRIES and STATES, a full refresh on POSTAL_CODES, and a fast refresh (if possible) on CUSTOMER_ADDRESSES. If Oracle cannot use a fast refresh on the CUSTOMER_ADDRESS table, it will perform a complete refresh instead.

  3. You can designate a specific, suitably sized rollback segment for the refresh.

  4. You can set the parameter, execute_as_user, to FALSE to force Oracle to refresh the snapshot under the privilege domain of the snapshot owner.

14.1.3.1.5 Updateable snapshot.

In the next example, we refresh the updateable snapshot DAILY_STORE_SALES.

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
	vSnapshotList(1) = 'DAILY_STORE_SALES'
	
	DBMS_SNAPSHOT.REFRESH(	tab => vSnapShotList,
				method => '?'
				push_deferred_rpc => FALSE);
END;

Since we set push_deferred_rpc to FALSE (the default is TRUE), the refresh will overwrite any local changes. The local changes will be visible again after the remote procedure call (RPC) pushes them to the master site and snapshot is refreshed again.

14.1.3.1.6 Parallel refreshes

In this example, the parallelism feature of Oracle8 allows us to use four processes to refresh the updateable snapshot DAILY_STORE_SALES:

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
	vSnapshotList(1) = 'DAILY_STORE_SALES'
	
	DBMS_SNAPSHOT.REFRESH(	tab => vSnapShotList,
				method => '?'
				parallelism => 4,
				purge_option = 2);
END;

The purge_option parameter controls how Oracle purges the snapshot site's deferred transaction queue; Oracle8 does not purge the queue automatically when the transactions propagate, so you must use DBMS_DEFER_SYS.SCHEDULE_PURGE (described in Chapter 17 ) to schedule a job to purge the queue, lest it become large and unmanageable. The purge_option parameter in REFRESH provides an opportunity to purge the queue of transactions associated with the updateable snapshot(s) you are refreshing.

NOTE: Purging the deferred transaction queue is not the same thing as purging a snapshot log!

14.1.4 Purging the Snapshot Log

The PURGE_LOG procedure deletes records from the snapshot log on a master table. You may wish to do this if the snapshot log becomes very large, or if you drop a subset of the snapshots for which the table is a master.

14.1.4.1 The DBMS_SNAPSHOT.PURGE.LOG procedure

Call the PURGE_LOG procedure to delete snapshot log records. The specification for the PURGE_LOG procedure follows:

PROCEDURE DBMS_SNAPSHOT.PURGE_LOG
   (master VARCHAR2
   ,num BINARY_INTEGER DEFAULT 1
   ,flag VARCHAR2 DEFAULT 'NOP' );

Parameters are summarized in the following table.

Name

Description

master

Name of the master table

num

Delete records required to refresh the oldest number of unrefreshed snapshot; default is 1

flag

Set to DELETE to guarantee that records are deleted for at least one snapshot regardless of the setting of num

The PURGE_LOG procedure does not raise any exceptions.

14.1.4.1.1 Examples

The following examples illustrate the use of the PURGE_LOG procedure. The first example shows the simplest form of the procedure; the only parameter is master:

BEGIN
	DBMS_REFRESH.PURGE_LOG( master => 'COUNTRIES' );
END;

Since the call uses the default value for num, 1, it will purge the snapshot log records required for a fast refresh of the least recently refreshed snapshot -- that is, the most stale snapshot.

This example shows the use of several parameters:

BEGIN
	DBMS_REFRESH.PURGE_LOG
        ( master => 'COUNTRIES', num => 5, flag => 'DELETE' );
END;

In this example, the procedure deletes snapshot log records required for a fast refresh of the five most stale snapshots. However, since we have set the flag parameter to DELETE, the call is guaranteed to delete the records for at least one snapshot, even if the table masters fewer than five snapshots.

NOTE: To delete all records from a snapshot log, set the num parameter to a high value (greater than the number of snapshots mastered to the master table, specified in the master parameter).

14.1.5 Reorganizing Tables

Occasionally a DBA must reorganize a table -- in other words, coalesce its extents and reduce row chaining. Two new programs in Oracle8 allow you to reorganize a master table without invalidating its snapshot log: BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION. Therefore, you do not have to perform complete refreshes of the table's snapshots after it is reorganized. To take advantage of this new feature, you must be using primary key snapshots.

14.1.5.1 The DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION procedure (Oracle8 only)

If you are reorganizing a table, call the BEGIN_TABLE_REORGANIZATION procedure before reorganizing the table, and the END_TABLE_REORGANIZATION procedure when you are finished. The specification for BEGIN_TABLE_REORGANIZATION follows:

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

Parameters are summarized in the following table.

Name

Description

tabowner

Owner of the master table

tabname

Name of the master table being reorganized

This procedure does not raise any exceptions.

14.1.5.2 The DBMS_SNAPSHOT.END_TABLE_REORGANIZATION procedure (Oracle8 only)

Call the END_TABLE_REORGANIZATION procedure when you are finished reorganizing a table. The specification for END_TABLE_REORGANIZATION follows:

PROCEDURE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION
   (tabowner IN VARCHAR2

Parameters are the same as those for BEGIN_TABLE_REORGANIZATION. This procedure does not raise any exceptions.

14.1.5.2.1 Examples

The following examples illustrate how to use these procedures as part of a table reorganization. The first example shows the steps for reorganizing a master table using truncation.

  1. Call DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION:

    BEGIN
    	EXECUTE DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (
    		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
    END;
  2. Back up the table by exporting it, or spooling it to a flat file.

  3. Truncate the master table, preserving the snapshot log:

    TRUNCATE TABLE countries PRESERVE SNAPSHOT LOG;
  4. Restore the table from the export file or flat file.

  5. Call DBMS_SNAPSHOT.END_TABLE_REORGANIZATION:

    BEGIN
    	EXECUTE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (
    		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
    END;

The next example shows the steps for reorganizing a master table using RENAME TABLE.

  1. Call DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION:

    BEGIN
    	EXECUTE DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (
    		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
    END;
  2. Rename the table:

    RENAME TABLE countries TO countries_pre_reorg;
  3. Create a new version of the table:

    CREATE TABLE countries AS SELECT * FROM countrie_pre_reorg;
  4. Call DBMS_SNAPSHOT.END_TABLE_REORGANIZATION:

    BEGIN
    	EXECUTE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (
    		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
    END;
  5. Recreate any triggers that were defined on the table.

In both of these examples, snapshots will be able to use the snapshot log for fast refreshes after the table reorganization is complete.

14.1.6 Registering Snapshots

One of the most significant improvements in Oracle8 is the automatic registration of snapshots at the master site. In Oracle7, there was no easy way to determine the location -- or even the existence -- of snapshots with master table(s) in your instance. But when you create a snapshot in Oracle8, Oracle puts a record in the DBA_REGISTERED_SNAPSHOTS data dictionary view. Similarly, when you drop a snapshot, Oracle deletes the record from DBA_REGISTERED_SNAPSHOTS.

The REGISTER and UNREGISTER procedures let you manually maintain this data dictionary view, shown in Table Table 14.2 .


Table 14.2: DBA_REGISTERED_SNAPSHOTS Data Dictionary View

Column Name

Description

OWNER

Snapshot owner.

NAME

Snapshot name.

SNAPSHOT_SITE

Global name of database where snapshot resides.

CAN_USE_LOG

If YES, then snapshot refreshes can use snapshot log.

UPDATABLE

If YES, then snapshot is an updateable snapshot.

REFRESH_METHOD

Refresh method; either ROWID or PRIMARY KEY.

SNAPSHOT_ID

Unique ID of snapshot used for fast refreshes.

VERSION

Version of the snapshot. Possible values are REG_UNKNOWN, REG_V7_GROUP, REG_V8_GROUP, and REG_REPAPI_GROUP.

QUERY_TXT

Text of the snapshot's query.

14.1.6.1 The DBMS_SNAPSHOT. REGISTER_SNAPSHOT procedure (Oracle 8 only)

Generally, the registration and unregistration of snapshots is automatic if both the master and snapshot databases are Oracle8. However, in case the snapshot site is running Oracle7, or if the automatic registration fails, you can use the Oracle8 procedure, REGISTER_SNAPSHOT, to register the snapshot manually.

NOTE: The registration of snapshots is not mandatory; it records data in DBA_REGISTERED_SNAPSHOTS that is for informational use only. You should not rely on the contents of this data dictionary view.

The specification for the REGISTER_SNAPSHOT procedure is as follows:

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

The REGISTER_SNAPSHOT procedure is overloaded; snapshot_id is a DATE type if the snapshot site is an Oracle7 database, and BINARY_INTEGER if it is an Oracle8 database. Parameters are summarized in the following table.

Name

Description

snapowner

Owner of the snapshot.

snapname

Name of the snapshot.

snapsite

Global name of snapshot site database instance.

snapshot_id

ID of the snapshot. Use DATE datatype for Oracle7 snapshot sites, BINARY_INTEGER for Oracle8 snapshot sites. The snapshot_id and flag parameters are mutually exclusive.

flag

PL/SQL variable dictating whether future moves and creates are registered in the qry_text parameter; this flag does not appear to be used.

qry_text

Up to 32000 characters of the text of the snapshot query.

rep_type

Binary integer indicating the version of the snapshot. Possible values are:

  • reg_unknown = 0 (the default)

  • reg_v7_group = 1

  • reg_v8_group = 2

  • reg_repapi_group = 3

REGISTER_SNAPSHOT does not raise any exceptions.

14.1.6.1.1 Example

Registration of Oracle7 snapshots is never automatic; you must call REGISTER_SNAPSHOT if you want to see Oracle7 snapshots in the DBA_REGISTERED_SNAPSHOTS data dictionary view. You must provide all of the information you want to see. In the following example, you register a snapshot from an Oracle7 snapshot site to an Oracle8 master:

BEGIN
	DBMS_SNAPSHOT.REGISTER_SNAPSHOT(
		snapowner	=> 'SPROCKET',
		snapname	=> 'COUNTRIES',
		snapsite	=> 'D7CA.BIGWHEEL.COM',
		snapshot_id	=> sysdate,
		flag		=> 0,
		qry_text	=> 'SELECT * FROM countries@D8CA.BIGWHEEL.COM',
		rep_typ	e	=> reg_v7_group);
END;

14.1.6.2 The DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT procedures (Oracle8 only)

The UNREGISTER_SNAPSHOT procedure is the flip side of the REGISTER_SNAPSHOT procedure. You use UNREGISTER_SNAPSHOT when you need to manually unregister a snapshot. This procedure unregisters snapshots at the master site, regardless of whether they were registered manually or automatically.

The specification is as follows:

PROCEDURE DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT
   (snapowner IN VARCHAR,
    snapname IN VARCHAR2,
    snapsite IN VARCHAR2)

See the description of parameters under the REGISTER procedure.

UNREGISTER_SNAPSHOT does not raise any exceptions.

NOTE: Unregistering a snapshot has no effect on the snapshot itself.

14.1.6.2.1 Example

In this example, we unregister the snapshot that we created in the previous section:

BEGIN
	DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT(
		snapowner	=> 'SPROCKET',
		snapname	=> 'COUNTRIES',
		snapsite	=> 'D7CA.BIGWHEEL.COM');
END

For a detailed example, see the mastsnap.sql file on the companion disk. This script lists registered snapshots at a master site, including their last refresh time. It requires Oracle8.


Previous: IV. Distributed Database Packages Oracle Built-in Packages Next: 14.2 DBMS_REFRESH: Managing Snapshot Groups
IV. Distributed Database Packages Book Index 14.2 DBMS_REFRESH: Managing Snapshot 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