| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Materialized view refresh problem
OS: Windoze NT 4.0 SP6
Oracle 8.1.7.4
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
from MLOG$_GHIST.
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.
Hmm...
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.
Jared
 |
 |