Oracle Distributed Systems

Creating Updateable Snapshots

Updateable snapshots require components at both the master site and the snapshot site. To illustrate the procedure, I’ll trace the steps required to create an updateable snapshot on the table SPROCKET.DAILY_SALES defined as follows:

SQL> desc daily_sales
 Name             Null?    Type
 ---------------- -------- ----
 SALES_ID         NOT NULL NUMBER(9)
 DISTRIBUTOR_ID   NOT NULL NUMBER(6)
 PRODUCT_ID       NOT NULL NUMBER(9)
 UNITS            NOT NULL NUMBER(9,2)
 REGION           NOT NULL VARCHAR2(3)
 AUDIT_DATE       NOT NULL DATE
 AUDIT_USER       NOT NULL VARCHAR2(30)
 GLOBAL_NAME      NOT NULL VARCHAR2(20)

Each retail outlet of the fictitious Bigwheel Bicycle company updates a local snapshot of this table with each customer purchase. The outlet stores send their data back to the headquarters database each evening. For the purposes of our example, suppose that the headquarters database is named PHQS.BIGWHEEL.COM and that the retail store’s database is named PSFO.BIGWHEEL.COM.

Preliminary Steps

Before creatingupdateable snapshots, the master and snapshot databases must be configured for replication as described in Chapter 12. In addition to running the catproc.sql and catrep.sql scripts at both sites, you must ensure the following:

• Replication administrator accounts exist with proper privileges at the master and all snapshot sites. (Typically, the replication administrator account is REPADMIN.)

• Database links must be in place. The links from the snapshot site to the master site must connect to an account that either is the owner ...