Determining where or not a MV can or can not be built as "ON COMMIT"
of "FAST" refreshable can be tricky. There are more rules than I care
to remember. I basically deal with it on a case by case basis. I
remember that to use "ON COMMIT" the MV must have single table
aggregate or a join (much like your MV), though I have no problem
using the "ON COMMIT" without aggregate or join with 9i. You can use
DBMS_MVIEW.explain_mview to help you decide what is possible with an
MV - though I can not remember if 8.1.7 has this package.
ewong_at_mail.com (Ed Wong) wrote in message news:<a5ae1554.0302061912.b54827d_at_posting.google.com>...
> 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
Received on Fri Feb 07 2003 - 00:43:31 CST
