Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: truncating snapshots

RE: truncating snapshots

From: A. Bardeen <>
Date: Tue, 18 Sep 2001 15:05:24 -0700
Message-ID: <>


OK, I think I see what you're trying to accomplish. By truncating the snapshot between refreshes, the refresh is essentially populating the snapshot with only the changed rows from the master site.

Probably not supported, but I can't see that it would cause any real problems. The way the refresh mechanism works, the missing rows on the snapshot site shouldn't be a problem.

I see a couple of potential problems, however.

  1. The refresh will pull over all changed rows on the master site, in your case inserts AND updates. So if a row does get updated on the master site, then it will get refreshed to the snapshot site and your counts will be off since you'll be treating the update of an existing row as a newly inserted row.

It all depends on how critical the numbers are for the developers, because I can assure you it's only a matter of time before someone updates rows on a table that should only have inserts ;)

2. If a fast refresh fails this requires that the next refresh is a complete refresh, or the snapshot is recreated, so you will not have a way of getting just the set of changed rows. Your procedure will need to be able to detect this and perform the joins against the entire table again.

In the long run you're probably much better off developing your own trigger to populate another table or setting a flag, as you mentioned. Just because it works today doesn't mean that it will work in a newer release if they change the refresh mechanism.


Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information
Please see the official ORACLE-L FAQ:
Author: A. Bardeen

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Sep 18 2001 - 17:05:24 CDT

Original text of this message