Re: ** mv log

One comment here: due to the fact that the syntax two statements below

> SQL> ALTER MATERIALIZED VIEW LOG ON tbl MODIFY p VARCHAR2(20);
> SQL> ALTER MATERIALIZED VIEW mv_tbl MODIFY p VARCHAR2(20);

is not reflected in the documentation (i.e. it could be that it's not fully supported), I would suggest to consider two options:

1. contact tech. support to be sure that your DB will be in the right (i.e. supportable) state in case you decide to simplify the process and do column(s) modification. I believe that internally everything would be done in a right way

or

b) you can stick with dropping and recreating all affected objects mviews/mview logs -- sure those have to be refreshed then using 'C'omplete method.

There is one major drawback of mviews maintenance, one have to deal with the fact that due to complexity of the mview object in most cases when any modification is required the mview has to be dropped and recreated. It's really painful in case nested mviews are used and there is a lot of aggregated data, which, unfortunately, has to be refreshed. In some cases 'ON PREBUILT' mviews can be considered as an option.

HTH,

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
http://www.freelists.org/webpage/oracle-l