For dynamic tables that require refreshing daily, we can specify that the table is refreshed at 11:00 AM each day:

CREATE SNAPSHOT cust_snap1
REFRESH FAST
    START WITH SYSDATE
    NEXT SYSDATE+11/24
AS SELECT cust_nbr, cust_name FROM customer@hq WHERE department = 'NY';

In addition to using the time range specified in the CREATE SNAPSHOT syntax, you can also use Oracle stored procedures to get the same result. If you have run the dbmssnap.sql script, you could also refresh a snapshot by issuing the following command:

EXECUTE dbms_snapshot.refresh('customer','c');  /* complete refresh */
EXECUTE dbms_snapshot.refresh('customer','f');  /* forced   refresh */
EXECUTE dbms_snapshot.refresh('customer','?');  /* fast     refresh */

Using Triggers To Update Snapshots

But what about replicated tables that require faster propagation? Oracle version 7.3 offers updatable snapshots, but users of previous releases of Oracle can also use database triggers to simulate the realtime propagation of changes from a master table to replicated tables. In the following example, an update trigger has been placed on the customer tables, and relevant changes will be propagated to the New York branch:

CREATE TRIGGER add_customer
    AFTER INSERT ON CUSTOMER
AS
IF :dept = 'NY' THEN
(INSERT INTO customer@NY
    VALUES(:parm1, :parm2,:parm3);
);

But what can we do about rows that are deleted from the customer table? Using the same technique, a delete trigger can be placed on the customer table to remove rows from the replicated tables:

CREATE TRIGGER delete_customer
    AFTER DELETE ON CUSTOMER
AS
IF :dept = 'NY' THEN
(DELETE FROM customer@NY
    WHERE
    cust_nbr = :customer_parm
);

Using Snapshots To Propagate Subsets Of Master Tables

As we have seen, snapshot replication is very handy for taking a master and copying it to a remote location. But what if we only want to replicate a portion of the tables? Yes, Oracle provides a method for excluding certain rows and columns from the replicated table. For example, let’s assume that we are replicating a central employee table for use by our New York branch. However, we only want to replicate employee records for those who work at the New York branch, and we want to exclude confidential columns such as the employee’s salary. Here is how a snapshot would appear:

CREATE SNAPSHOT emp_ny
REFRESH FAST
    START WITH SYSDATE
    NEXT NEXT_DAY(trunc(sysdate),'TUESDAY')+6/24
AS
    SELECT
            emp_nbr,
            emp_name,
            emp_phone,
            emp_hire_date
    FROM emp@hq WHERE department = 'NY';

Asynchronous Updating Of Oracle Tables

The latest fad in the database marketplace is the widespread replication of data to mobile remote servers for updating purposes. The remote servers are completely disconnected from the central server, and the updates usually occur on laptop servers. At any time, the user of the laptop can dial in to the central server and transfer their updates (Figure 7.2).

Figure 7.2  Asynchronous updating mechanisms.

This approach has been very popular with Oracle-based sales force automation (SFA) systems that strive to provide field salesmen with current information from the centralized host without requiring them to continually dial in to the server. Here is how they work.

Daily changes to the centralized host are collected and distributed to the remote laptop database whenever the salesmen dial in to the host and request a refresh. As the salesmen make changes on their laptops, they are able to feed these changes to the central host whenever they dial in. While this may seem like a sound idea on the surface, some very serious problems can occur with asynchronous updating:

•  How are update anomalies handled?

•  What happens when a change based on outdated information is made on a remote laptop?

•  How do we develop a mechanism for synchronizing all of the remote laptops?

The asynchronous approach is used almost exclusively with distributed sales systems that require salespeople in the field to have current information without having to connect to a central server via modem.

The idea is to define a subset of the database for each mobile user, and then develop a mechanism that ensures that the disconnected laptop is updated each time the salesperson connects to the central database. In addition, the updates must occur quickly to minimize connect time. Most of these systems apply a date-time stamp to all relevant rows, using this date-time key to quickly retrieve the data from the master tables. It now becomes obvious that every laptop is going to be out of sync with the master database to some degree.

At connect time, the system must also upload all changes that have been made to the mobile database, updating the master tables on the central server. The absence of database locks leads to some interesting update anomalies. These anomalies take two forms. The first one happens when a row is updated by two mobile laptops, and the upload of one row overlays the changes that were made by the other mobile laptop. The second type of anomaly occurs when outdated information is used as the basis for making a change while on the mobile laptop.

Most of the SFA products have developed their own rules for dealing with anomalies. These rules include:

•  Defer all update anomalies, writing them to a special area for manual resolution.

•  The most recent update (as indicated at upload time) supersedes any prior updates.

•  All end users are ranked, and the user with the highest rank supersedes all changes made by a lower-ranked employee.

Regardless of the method chosen, these tools are normally constructed using a relational database such as Oracle with a custom synchronization manager. The synchronization manager handles the downloading of information from the centralized server to the laptop and the uploading of changes to the main server.

For downloads, most of these tools prepackage the relevant information for the laptop user, keeping track of the last synchronization and writing changes to a special table. In this fashion, the laptop user does not have to wait while the server extracts the updates, and the updates can immediately be transferred and loaded into the laptop server.