Re: ORA-12054 when creating materialized view

Hi,

and even if you add count(*) changing your MV into materialized view with single-table aggregates,
you don't make it on commit refreshable, because there are a set of restrictions. And you violate one of them at least, namely:

Restrictions on Fast Refresh on Materialized Views with Single-Table Aggregates
- They cannot have a WHERE clause.

Moreover, in General Restrictions on Fast Refresh stated that: The WHERE clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No __selection predicates__ on individual tables are allowed.

Regards,

--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com



"Kirtikumar Deshpande" <kirtid_at_verizon.net> wrote in message
news:3E4339A4.2060800_at_verizon.net...


> Your select statement does not have any aggregation. Since this is a

> single table MV, you at least need a count(*) in the select statement.

>

>

> - Kirti

>

> Ed Wong wrote:

> > When creating a materialized view, I received "ORA-12054 : cannot set

> > the ON COMMIT refresh attribute for the materialized view" error

> > message.

> >

> > create materialized view target_mview

> > tablespace target_data

> > storage (pctincrease 0)

> > build immediate

> > refresh fast on commit

> > enable query rewrite

> > as select * from target

> > where targetid = 100 and targetname = 'XYZ';

> >

> > ORA-12054: cannot set the ON COMMIT refresh attribute for the

> > materialized view

> >

> > "target" is a physical table and the query is pretty simple so I have

> > no idea why I get this error.  I've granted "global query rewrite".

> >

> > I am using 8.1.7 EE on Sun Solaris.  Please help.  Thanks.

> >

> > ewong

>