In my development environment I created a materialized view as
CREATE MATERIALIZED VIEW cc_cdd_view
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
SELECT CK006.CK006_CC_CODE, CK006.CK006_CC_NAME,ck006.ck006_modern_cc,CK001.CK001_CDD_CODE, CK001.CK001_CDD_DESC
FROM CK006_CC_MST CK006,
WHERE CK006.CK004_CONSTCY_CODE = CK009.CK004_CONSTCY_CODE
AND CK009.CK003_GRC_CODE = CK008.CK003_GRC_CODE
AND CK008.CK002_CDC_CODE = CK007.CK002_CDC_CODE
AND CK007.CK001_CDD_CODE = CK001.CK001_CDD_CODE;
Here this user is granted with "create materialized view" and "query rewrite" in addition to the connect and resource roles. The init parameter values related to this are kept as
QUERY_REWRITE_INTEGRITY=TRUSTED. Here I did not create any materialized view logs. Whenever any change is there in any of the related tables it is getting properly reflected in the materialized view immediately after commit.
In my production server in addition to all these privilages ,DBA role is also granted . Now when I try to create the above mentioned materialized view it gives the error, "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view".
What could be the reason that it is happening only in Production server ?. Is there any more settings to be done for creating materialized views ?.
Thanks in advance