Note in-line:
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 2nd
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:413FB4B4.36EC_at_yahoo.com...
> M Rothwell wrote:
> >
> > Given the following:
> >
> > Table XYZ on server A
> > MViewLog MLOG$_XYZ on server A
> > MView XYZ on server B
> >
> > If MView XYZ on server B is created as:
> >
> > Create Materialized view XYZ
> > build immediate
> > refresh fast with primary key
> > as select * from xyz_at_serverA;
> >
> > when I refresh MView XYZ, does it do anything to the MVeiw log on server
> > A? Does that log continue to grow and grow as table XYZ is updated even
> > tho I have refreshed my MView on server B?
> > If so, and I am concerned about space, then how would I go about keeping
> > the MView log file small and not risk any data inconsitancies? I will
> > have the MView in a refresh group updating 1 or maybe 2 times per day.
> >
> > Both server A and B are running O9i R2 on HP-UX boxes.
> >
> > Thanks
>
> As rows are refreshed, the related entries should be removed from the
> materialised view log.
The word "SHOULD" is, of course, important.
If, for some reason, Oracle thinks that the materialized view log
is also there to support a second materialized view, it will not
delete the log entries - so the log table will grow. This can happen
quite easily (or used to in earlier versions) when things went a bit
wrong and DBAs tried to clean up the mess manually - sometimes
a materialized view would be dropped, but remain registered at
the master database - which would then allow the log to grow
indefinitely because the "ghost" MV never asked to be refreshed.
Another important point to remember is that the log is just a table -
if it becomes very large at some point in time, it will stay large
(even if it is nearly empty) forever after. (Has anyone tried using
the COMPACT option from 10g on an MV log to address this
issue ?)
>
> hth
> connor
> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> ISBN: 1590592174
>
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
> Coming Soon! "Oracle Insight - Tales of the OakTable"
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> and...he will sit in a boat and drink beer all day"
>
> ------------------------------------------------------------
Received on Thu Sep 09 2004 - 16:05:47 CDT
