[Oracle-l] MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

On 11/28/06, Freeman, Donald wrote:

I have one of these to do today. We are adding two columns on a table in
the source database. I have a materialized view of that table on my
warehouse DB. I was just planning on running create or replace mview as
select * from source; I do a full refresh anyway. Is that a problem for
you?

-----Original Message-----
*From:* oracle-l-bounce_at_freelists.org [mailto:
[email protected]] *On Behalf Of *Jared Still
*Sent:* Tuesday, November 28, 2006 2:52 PM
*To:* marquezemail_at_gmail.com
*Cc:* oracle-l
*Subject:* Re: MODIFY Materialized View Definition without dropping it (or
the MLog) after base table alter?

I just tried this on Oracle 10.2.0.2 on Linux:

Create table
Create MV log on table
Create Mv on source table ( all in same account on one database)

Add column to source table
Add same column to MV table
unregister and register snapshot with a new query via dbms_mview.register

A complete refresh still works for the original columns, but does not
update
the new column.

Adding the query via dbms_mview did not have any effect on
sys.snap$.query_txt
(as seen in dba_snapshots)

Updating sys.snap$ directly with a new query did not fix it either.

However, monkeying with snap$ did break the DD, as the following
error will show:

11:48:49 SQL> /
alter materialized view mv_target compile
*
ERROR at line 1:
ORA-12003: materialized view "MV_TARGET" does not exist

Oops. I guess that is why we don't modify the Data Dictionary. :)

There's probably other ways to go about this, but this is
the only one I tried.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist