Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

alter materialized view

inDiscoverFeb 19 2014 — edited Feb 20 2014


Guys,

I had created a materialized view. For example as below.

CREATE MATERIALIZED VIEW schema.mat_repo_view

BUILD IMMEDIATE

REFRESH COMPLETE

ON DEMAND

disable query rewrite

as

select * from table1

I need to alter this view to change my query inside this view. So can I use the alter syntax as below.

ALTER MATERIALIZED VIEW new_employees

AS

select * from table2.

Or do I need to use the same parameters (like refresh clause , build clause etc....) as I used while creating my View. Or I need to specify the sames clauses again while altering the view.(like refresh clause , build clause etc....)

Thanks

Comments

KarK

Hi,

I don't think so, you can alter only any of the parameters used in the view.

SQL>ALTER MATERIALIZED VIEW schema.mat_repo_view  enable query rewrite ;

Materialized view altered.

Instead of that create another materialized view.

Check the below link:

ALTER MATERIALIZED VIEW

Partha Sarathy S 
ALTER MATERIALIZED VIEW new_employees


AS


select * from table2.

You mean to say you are modifying the query which builds the materialized view? No you cannot do that. Why don't you drop and recreate the materialized view. Look at this thread for a similar discussion.

changing query that builds a materialized view

AnnEdmund

No, you cannot alter the query of a materialized view without dropping it.

The CREATE MATERIALIZED VIEW syntax does not support that feature.

The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways:

  1. To change its storage characteristics
  2. To change its refresh method, mode, or time
  3. To alter its structure so that it is a different type of materialized view
  4. To enable or disable query rewrite
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 20 2014
Added on Feb 19 2014
3 comments
740 views