Use Primary Key Snapshots Replication in Oracle

When you create a snapshot, Oracle creates another table to keep track of changes to the master table. This new table name is the same as the master table name, except that it is prefixed by MLOG$. For example, a customer master log entitled CUSTOMER would have a customer snapshot master log called MLOG$CUSTOMER. Whenever a change is made to the master table, Oracle fires an internal trigger to post the change to the MLOG$_CUSTOMER table. When the refresh interval is met, Oracle goes to the master log to gather the IDs of all rows that have changed and propagates the IDs to the remote databases. In Oracle7, all snapshots were ROWID snapshots^[1] . That is, the logs contained the ROWIDs of the changed rows. These ROWIDs were used to find the changed rows in the master table when it was time to propagate the rows. In Oracle, ROWID snapshots have been replaced by primary key snapshots^[2]. With primary key snapshots, the snapshot log table holds the primary key for each row that has changed.

Note: In certain situations, particularly those involving data warehousing, snapshots may be referred to as materialized views. This section retains the term snapshot.

• Snapshot Log: The snapshot log in a dump file is imported if the master table already exists for the database to which you are importing and it has a snapshot log. When a ROWID snapshot log is exported, ROWIDs stored in the snapshot log have no meaning upon import. As a result, each ROWID snapshot's first attempt to do a fast refresh fails, generating an error indicating that a complete refresh is required. To avoid the refresh error, do a complete refresh after importing a ROWID snapshot log. After you have done a complete refresh, subsequent fast refreshes will work properly. In contrast, when a primary key snapshot log is exported, the values of the primary keys do retain their meaning upon import. Therefore, primary key snapshots can do a fast refresh after the import.
• Snapshots: A snapshot that has been restored from an export file has reverted to a previous state. On import, the time of the last refresh is imported as part of the snapshot table definition. The function that calculates the next refresh time is also imported. Each refresh leaves a signature. A fast refresh uses the log entries that date from the time of that signature to bring the snapshot up to date. When the fast refresh is complete, the signature is deleted and a new signature is created. Any log entries that are not needed to refresh other snapshots are also deleted (all log entries with times before the earliest remaining signature).

When you restore a snapshot from an export file, you may encounter a problem under certain circumstances. Assume that a snapshot is refreshed at time A, exported at time B, and refreshed again at time C. Then, because of corruption or other problems, the snapshot needs to be restored by dropping the snapshot and importing it again. The newly imported version has the last refresh time recorded as time A. However, log entries needed for a fast refresh may no longer exist. If the log entries do exist (because they are needed for another snapshot that has yet to be refreshed), then they are used, and the fast refresh completes successfully. Otherwise, the fast refresh fails, generating an error that says a complete refresh is required.
In Oracle, all snapshots are primary key snapshots by default. If you want to use the Oracle ROWID snapshot, you must add REFRESH WITH ROWID to the snapshot definition.

• Advanced Primary Key Snapshots
  Primary key snapshots may be quite complex, and they may even contain sub queries. This is useful in cases where a reference to another table is required to constrain the snapshot. The following code is an example of a snapshot with a WHERE clause containing a correlated sub query:
  

  CREATE SNAPSHOT 
     BAD_CUSTOMER
  AS
   SELECT * 
  FROM CUSTOMER@DENVER MASTER
   WHERE EXISTS
     (SELECT 1 FROM BAD_CREDIT@DENVER BAD
      WHERE MASTER.CUST_ID = BAD.CUST_ID);
  

  
  In the example above, we have created a primary key snapshot called BAD_CUSTOMER. The snapshot will keep track of customer rows where a corresponding row exists in the BAD_CREDIT table.
  Now that we understand the basics of Oracle primary key snapshots, let us take a look at Oracle's new updateable snapshot feature.

[1] ROWID snapshot: A copy of a table. Changed rows are refreshed by retrieving the row using its ROWID.

[2] Primary key snapshot: A copy of a table or view. Changed rows are refreshed by retrieving the row using its primary key value.: