I am trying to create this MVIEW on my replication database and get the erorr below. Can somebody tell me what is wrong and how I can correct it.
SQL> CREATE PUBLIC SYNONYM abc for pbh.abc;
CREATE MATERIALIZED VIEW abc
REFRESH ON COMMIT
AS SELECT * FROM abc@PRI;
$ oerr ora 12054
12054, 00000, "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.
I did some experimenting and finally found from Oracle that "on commit" you can use only if you have your master table in the same database where you are creating MView. Therefore, on commit is not suported in remote databases. Another way you can use remote database if you replace your line "REFRESH ON COMMIT" with "refresh fast on demand with primary key" and this works.