Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Materialized view refresh problem

Materialized view refresh problem

From: <>
Date: Thu, 29 Jan 2004 17:55:26 -0800
Message-ID: <>

OS: Windoze NT 4.0 SP6

Though I have already fixed this problem via brute force, I would like to find
out why this happened. ( I recreated the snapshot log and snapshot )

For some reason today, one snapshot of a group of 33 just quit working properly.

The update times in all_refresh at the MV end and the refresh times at the LOG
end were all correct, ie. they matched.

No DDL was done on either the source or target database, and indeed, I was not
even in the office when it started happening, so I can't blame myself. ;)

Let's call the table GHIST. The snapshot log MLOG$_GHIST on the source database would not be purged after a snapshot refresh from the target database.

There is only one set of snapshots against the MV logs on the source side. ie. querying sys.slog$ returned the same number of rows as all_snapshot_logs,
and the table names matched.

Executing a complete refresh on the GHIST snapshot, and executing DBMS_REFRESH.PURGE_LOG()
on the offending log MLOG$_GHIST on the source side did not remove the entries

Now it gets even more interesting.

It was decided to just drop the refresh group, drop the snaphot log for GHIST as well as
the snapshot for GHIST.

I ran the scripts to drop the group, recreate the log and mv, and voila, same problem.


Check the logs for the script, everything did exactly as it should.

Problem is, the MLOG$_GHIST table still has all the row in it prior to dropping the log,
which should not be possible.

So, the statement 'drop snapshot log on ghist;' reports success, but doesn't actually
do anything.

What was necessary was to manually drop the table MLOG$_GHIST, and then run
the scripts to recreate everything.

This problem was explained by bug 1610709, which states that you must be logged in as the owner of the log ( I wasn't ) for the drop snapshot log to work properly.

What isn't explained is why the refresh quit working in the first place, though the views
for dba_jobs, all_refresh and sys.slog$ all report that everything is working.

BTW, these MV's are 'fast refresh with rowid'.

Any ideas welcome.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Jan 29 2004 - 19:55:26 CST

Original text of this message