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

From: Freeman, Donald <dofreeman_at_state.pa.us>
Date: Tue, 28 Nov 2006 15:27:13 -0500
Message-ID: <51327ABA927BEF4B96590554CEA7832C060B97D9@enhbgpri05.backup>

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 
	

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

Received on Tue Nov 28 2006 - 14:27:13 CST