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@xxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto: oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still *Sent:* Tuesday, November 28, 2006 2:52 PM *To:* marquezemail@xxxxxxxxx *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@xxxxxxxxx> 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