Re: Snapshot log problem
Date: Fri, 16 Jan 2009 10:52:59 -0800 (PST)
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?
To verify your snapshot refresh was successful you can run this query:
select owner, name, table_name, last_refresh, error
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