Dear All,

In my development environment I created a materialized view as

CREATE MATERIALIZED VIEW cc_cdd_view
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
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,
CK009_GRC_CONSTCY_MST CK009,
CK008_CDC_GRC_MST CK008,
CK007_CDD_CDC_MST CK007,
CK001_CDD_MST CK001
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_ENABLED=TRUE
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

Louis.