ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Error Description:
The scenario is I have created a materialized view through database link over a table reside on the remote database.

I used the ON COMMIT option and it fails with ORA-12054 as below.

SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on commit
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';
from PHONES@lnxdb where upper(IS_SOLD)='Y'
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

In our case we satisfy the second restriction and hence error comes.

Solution of the Problem
To implement the solution you have to provide ON DEMAND clause. You have to create as below.
SQL> create materialized view scott.mv_phones
2 TABLESPACE users
3 REFRESH FAST on demand
4 WITH rowid
5 AS select ID, PASS,
6 CONN_EXPIRY_DATE, CONN_STATUS
7 from PHONES@lnxdb where upper(IS_SOLD)='Y';