MVIEW on commit refresh condition

[DontBShy]

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

[Mr.Hanky]

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

MH

[DontBShy]

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

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

[Mr.Hanky]

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

[DontBShy]

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

I'm still looking for those condition!

[DontBShy]

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 !

[Mr.Hanky]

Oh well, glad you found it.

MH

[marcio.68]

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

[marcio.68]

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