Materialized View logs issue

[toddyl]

Hi,

I have serveral Materialized views which are based on several tables. Each of these tables have a Materialized view log associated with them. There's been trouble in the past and these views have been dropped, recreated, altered etc. After much change the views now appear to be working fine.

However, it has recently come to light that there are a few materialized view logs existing in the system that are not registered with any view.

I cannot seem to find a reason as to why this has happened and how they came to exist (as they have the same name as other materialized views I think)

Does anyone know if it is safe to just drop them or what should I do?

Thanks.

 

[karluk]

I don't see any reason not to drop the materialized view logs, assuming there really aren't any materialized views which are dependent on them. After all, you can always recreate them later if you need them. I guess the question is how likely is it that you missed a dependency. Can you provide the queries you did to identify the unused mview logs?

 

[toddyl]

SQL as requested:

SELECT mowner,

MASTER,

snapid,

NVL (r.snapsite, 'not registered') snapsite,

snaptime

FROM SYS.slog$ s,

SYS.reg_snap$ r

WHERE s.snapid = r.snapshot_id(+)

AND s.snaptime < (SYSDATE - 8)

 

[karluk]

As it happens, I had to do this same cleanup not too long ago. Your query identifies materialized views which haven't been refreshed in the last eight days, but for which there is a materialized view log holding records in expectation that the materialized view will eventually need to be refreshed. One way this can happen is if a materialized view was dropped from the snapshot site, but for some reason cleanup didn't occur on the master site.

Obsolete materialized views that are preventing logs from getting purged should definitely be cleaned up. Otherwise your materialized view log will continue to grow, adding records in anticipation of a refresh that will never happen.

The way to do the purge is with the "dbms_snapshot" package. The command I used for my cleanup was

Execute dbms_snapshot.purge_log('MOWNER.MASTER', 1);

"Mowner" and "master" are both taken from the output of the query you ran. You have to specify both, enclosed in single quotes and separated by a period. If you don't do so, you more than likely won't get an error, but your log won't get cleaned up either.

Please be careful with this cleanup. I suggest you do only one "dbms_snapshot.purge_log" command at a time, and then rerun your "not refreshed in the last 8 days" query. That will show you exactly what got purged. Stop doing purges as soon as you've gotten rid of all the unregistered snapshots. If you purge too much, Oracle will start purging log records of materialized views that are still being refreshed, potentially causing the refreshes to fail.

 

[toddyl]

Karluk,

Thanks for this feedback, I'll give it a try and let you know how I get on.

One question though, will this procedue remove the 'useless' materialized view logs or will it just empty them of their contents?

Thanks,

Tom

 

[karluk]

The materialized view logs will remain, but will be purged of the rows which were waiting for unregistered views to refresh.

It's possible these logs really are "useless" and can be dropped, but nothing you've shown us so far indicates this. In particular, the query you ran only shows views which haven't refreshed in the last eight days. It tells you nothing at all about views which HAVE refreshed in the past eight days, including some which may very well be dependent on the logs you are thinking of dropping.