Re: MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

Chris hasn't said why avoiding a replace of the MView was important, but I'm guessing it was to avoid the full refresh.

On 11/28/06, Freeman, Donald <dofreeman_at_state.pa.us> 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:
> oracle-l-bounce_at_freelists.org] *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?
>
>
> On 11/28/06, Chris Marquez <marquezemail_at_gmail.com> wrote:
> >
> > Modify Snapshot without drop
> >
> > RH Linux
> > 9.2.0.5
> >
> > I need to alter my base table (add columns) and thus also the remote db
> > MView referring to it.
> >
> > I don't mind doing some RTFM and I have been on Metalink this morning
> > without much luck.
> >
> >
> 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
>
>

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

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