MVIEW on commit refresh condition

Hi,


I need to have an up to date copy of a table in another environment. In the master database I created a materialized view log on the master table:

CREATE MATERIALIZED VIEW LOG ON TIPS.POSITIONS WITH ROWID INCLUDING NEW VALUES;

Actually all applications use a DBLINK to access the positions table in the remote database. I tried to create a snapshot to have better performance:

CREATE MATERIALIZED VIEW MV_POSITIONS
REFRESH FAST ON COMMIT
AS
SELECT *
FROM POSITIONS

An I got the error:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

What are those conditions?

I read in the oracle documentation about on commit refresh:
Restriction: This clause is supported only for materialized join views and single-table materialized aggregate views

Does this mean I can't do a on commit fast refresh on a single table??

Thanks

Did you create a materialized view log for the underlying table?
Did you try REFRESH FORCE ?

MH

Hi,
Yes I tried REFRESH FORCE and I got the same error. By :

Quote:

---

Originally posted by Mr.Hanky
Did you create a materialized view log for the underlying table?
Did you try REFRESH FORCE ?

MH

---

You're talking about which table? I created a log on the master table and I'm trying to create an on commit refresh snapshot for this table.

Thanks

I'm not sure but do you need to specify...

CREATE MATERIALIZED VIEW MV_POSITIONS
REFRESH FAST ON COMMIT
AS
SELECT *
FROM POSITIONS

BUILD IMMEDIATE;


after the from positions?

MH

This doesn't work.. The documentaion doesn't mention it...

I'm still looking for those condition!

Hi,

I got the answer for my problem:

8.1.6 Data Warehousing Guide (8-20) : ON COMMIT : Can be used with
materialized views on single table aggregates and materialized views
containing joins only.

9.0.1 Data Warehousing Guide (8-26) : ON COMMIT : Can be specified as long as the materialized view is fast refreshable (in other words not complex).

The query you listed is a single table non-aggregate and should thus
be able to be ON COMMIT refreshed in 9.x but not in 8.1.x.

I'm using 8.1.7.4... Dammit !

Oh well, glad you found it.

MH

if uses view materialized is necessary to set something in the configuration archive it Oracle?

SQL> CREATE MATERIALIZED VIEW vw_teste
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 as
5 SELECT codigo, nome
6 FROM marcio.teste@pd00
7 ;
SELECT codigo, nome
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL> CREATE MATERIALIZED VIEW vw_teste
2 PCTFREE 0 TABLESPACE usr
3 BUILD IMMEDIATE
4 REFRESH COMPLETE ON DEMAND
5 AS
6 SELECT codigo, nome
7 FROM marcio.teste@pd00
8 ;

Materialized view created.

BUT do not refresh

:confused: