HI,

Do you know how I can create MV with refresh on commit ?
(10.2.0.4)
Thanks in advance

1 CREATE MATERIALIZED VIEW MV_NAME_LIST
2 BUILD IMMEDIATE
3 REFRESH on commit
4 ENABLE QUERY REWRITE
5 AS
6 SELECT DISTINCT CODENAME, CODE, LABEL
7 FROM CTSDD.ITEM, CTSCODES.AGGREGATED_CODES
8* WHERE CODENAME = CODELIST
T18> /
FROM CTSDD.ITEM, CTSCODES.AGGREGATED_CODES
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized
view

"bob123" <nomail (AT) nowhere (DOT) com> a écrit dans le message de news: 4ca4ee6b$0$8109$426a34cc (AT) news (DOT) free.fr...
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.

Regards
Michel

On Thu, 30 Sep 2010 22:09:09 +0200, bob123 wrote:

You may try with DBMS_MVIEW.EXPLAIN_MVIEW. Materialized view cannot be
refreshed on commit unless it is capable of fast refresh. You should
create table called MV_CAPABILITIES_TABLE, best using
?/rdbms/admin/utlxmv.sql
and then explain the view. It will tell you precisely what are your
problems. I hear that it even may be documented somewhere. There is a
very detailed and a very nice example in Christian Antognini's book. I
know that reading books so out, so 20th century, but I confess to having
that bad habit.



--
http://mgogala.byethost5.com