Re: Snapshot log problem

From: ddf <oratune_at_msn.com>
Date: Fri, 16 Jan 2009 10:52:59 -0800 (PST)
Message-ID: <204413b8-49d4-42a3-aa6b-2b8de2119e49_at_t39g2000prh.googlegroups.com>

On Jan 15, 2:15 pm, CenturionX <darwinbaldr..._at_gmail.com> wrote:
> I really appreciate your help.
> What I’m intended to do is to check if the refresh of the snapshots
> was successful.
> So, I have two questions:
> 1] In Oracle 10g R2, how can I ensure that?
> 2] The MLOG$ tables keep the records all time or they are delete
> before the refresh?
>
> Thanks.

To verify your snapshot refresh was successful you can run this query:

select owner, name, table_name, last_refresh, error from DBA_SNAPSHOTS
where error <> 0;

'no rows selected' indicates error-free refreshes for all snapshots/ materialized views.

The materialized view logs are not purged; why would they be? They are created against the master tables and record data changes to those tables to allow fast refreshes of the snapshots/materialized views. TRUNCATE TABLE can purge those logs, but won't by default. You'd need to specify PURGE MATERIALIZED VIEW LOG in the TRUNCATE command before that would occur:

truncate table emp purge materialized view log;

You can substitute SNAPSHOT for MATERIALIZED VIEW and Oracle won't complain:

truncate table emp purge snapshot log;

David Fitzjarrell Received on Fri Jan 16 2009 - 12:52:59 CST