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

True, however the materialized view, I think, will be marked as invalid. Even though it will still work. I'm not sure if the invalidation happends only if the source table is dropped and recreated, or during 'alter table' operations as well. The invalid status can be cleared via

alter <materialized view> compile;

Ian

-----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 5:34 PM To: Alberto Dell'Era
Cc: marquezemail_at_gmail.com; oracle-l
Subject: Re: MODIFY Materialized View Definition without dropping it (or the MLog) after base table alter?

On 11/28/06, Alberto Dell'Era <alberto.dellera_at_gmail.com> wrote:

	I think there's a misunderstanding here, I was just asking to Chris
	whether the new column, that has to be added on the base table,
	possibly with a default value, has to be propagated to the MV also (so
	with the same value) or not. 
	
	Eg
	old mv : create materialized view as select a from t_at_dblink
	say you "alter table t add (new_column int default 42)"
	has the mv to be logically modified to
	create materialized view as select a, new_column from t_at_dblink
	or does it stay the same, ignoring new_column ?
	
	

Ah, I see.

If that were the case, there's little else to do.

Adding a column to the table does not invalidate any of the objects used for the materialized view, and it will still work properly.

--

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 28 2006 - 20:04:53 CST