I was wondering if somebody can help me out. I am trying to create a materialized view that would refresh on a commit to a master table.

My user on both machines (master and replication) is "pbh" and I granted DBA priviledges to "pbh" on both databases.

In my master database I did this:

SQL> desc aaa;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER(6)
B NUMBER(6)

SQL> CREATE MATERIALIZED VIEW LOG ON aaa
2 WITH SEQUENCE, ROWID, PRIMARY KEY
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> select count(*) from aaa;

COUNT(*)
----------
10000


In my replication database I did this and get the following error:

SQL> select count(*) from aaa@PRI;

COUNT(*)
----------
10000

SQL> CREATE MATERIALIZED VIEW aaa
2 REFRESH ON COMMIT
3 AS SELECT a,b FROM aaa@PRI;
AS SELECT a,b FROM aaa@PRI
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


Can somebody let me know what exactly the problem is and how I can fix this? All I want to do is have my table in the replicated database refresh automatically.

Thanks in advance to all who answer