Oracle7 Server Distributed Systems Volume II: Replicated Data

Creating a Replicated Object

Add a replicated object to your snapshot site by calling the procedure CREATE_SNAPSHOT_REPOBJECT in the DBMS_REPCAT package, as shown in the following example:

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
    sname    => 'accts_rec', 
    oname    => 'emp', 
    type     => 'snapshot', 
    ddl_text => 'CREATE SNAPSHOT accts_rec.emp FOR UPDATE AS 
                SELECT * FROM emp@acct_hq.hq.com WHERE 
                deptno > 500', 
    comment  => 'created on ...',
    gname    => 'acct')
    gen_obj_owner ==> 'REPADMIN';

In this example, Oracle creates a snapshot at the snapshot site of the EMP table located at the master site. This snapshot contains a subset of the rows in the EMP table; that is, it only contains the rows for those employees whose department numbers are larger than 500. For example, this might be all sales staff in the western region.

Attention: Notice that the object name and updatable snapshot name must be identical. The master table must be a replicated object registered at the master site.

In this example, the SQL statement necessary to create the snapshot is passed as an argument to this procedure. Alternatively, you could have created the snapshot before calling this procedure and simply have omitted the DDL.

Warning: If you create an updatable snapshot using the FOR UPDATE clause of the CREATE SNAPSHOT command, but do not create the snapshot as a replicated object by calling CREATE_SNAPSHOT_REPOBJECT, any changes that you make to the updatable snapshot will be lost when you refresh the snapshot.

In addition to creating the snapshot as a replicated object at the snapshot site, Oracle also adds the object name and type to the RepObject view at the local site. This view is used to determine which objects need to push their changes to the master site.

Because the replicated object in this example is of type SNAPSHOT and its associated master table uses row-level replication, Oracle installs the appropriate replication support at the snapshot site. For snapshots of tables using procedural replication, be sure to replicate the associated procedure or package at the snapshot site.

Attention: Although not required, you will typically want all snapshots at a given snapshot site to be in the same snapshot refresh group. To ensure that snapshots in the same refresh group are refreshed consistently, their associated master tables must be located at the same master site.

Additional Information: The parameters for the CREATE_SNAPSHOT_REPOBJECT procedure are shown in Table 12 - 107, and the exceptions are listed in Table 12 - 108.

Creating Non-Snapshot Objects

For objects other than snapshots, you must not supply the DDL. Oracle copies the object from the master site that you designated when you created the snapshot site. If the object already exists at the snapshot site, Oracle compares the two objects and raises a duplicateobject exception if they do not match.

Creating Snapshots

For snapshots, you can either precreate the snapshot, or supply the DDL as part of the CREATE_SNAPSHOT_REPOBJECT call. For information on creating read-only snapshots, refer to Chapter 3.

Updatable snapshots are created and deleted in the same manner as read-only snapshots. See Chapter 3. To create an updatable snapshot, simply add the FOR UPDATE clause to the CREATE SNAPSHOT statement as shown in the following example:

CREATE SNAPSHOT emp FOR UPDATE
   AS SELECT * FROM scott.emp@sales.ny.com;

Restrictions on Updatable Snapshots

Declarative constraints on snapshots and snapshot logs are not supported.

Snapshots of LONG columns are not supported.

Updatable snapshots must be simple snapshots; that is each row in the snapshot is based on a single row in a single remote table. A simple snapshot's defining query has no distinct or aggregate functions, GROUP BY or CONNECT BY clauses, subqueries, joins, or set operations.

Symmetric replication does not support replication of a subset of columns. All CREATE statements must be of the form

CREATE SNAPSHOT . . . FOR UPDATE
   AS SELECT * FROM . . .;

The following SQL statement is not supported:

CREATE SNAPSHOT . . . FOR UPDATE
   AS SELECT empno, ename FROM . . .;

Naming Updatable Snapshots

Naming conventions for updatable snapshots are the same as for read-only snapshots. See

Privileges Required to Create Updatable Snapshots

To create an updatable snapshot, you must have the following sets of privileges:

To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, CREATE TRIGGER, and CREATE VIEW system privileges, as well as SELECT privilege on the master tables.

To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege, as well as SELECT privilege on the master table. Additionally, the owner of the snapshot must have been able to create the snapshot.

To create the snapshot as a replicated object, you must have been granted EXECUTE privileges on SYS.DBMSOBJGWRAPPER at the master site.

Transaction Ownership

Under synchronous propagation, a transaction is owned by the owner of the trigger and will be propagated to the remote sit with that owner's privileges. You can change the ownership of the transaction, usually to the replication administrator who has full privileges at the remote site, by using GEN_OBJ_OWNER.