Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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

Oracle Database Tips by Donald BurlesonJune 4, 2015

Question: Can you tell me why I would get the ORA-12054 error and what I need to do in order to fix an ORA-12054:

ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Answer:  This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported 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.

Also see my related notes on  materialized view refresh fast.

The docs say this about the ORA-21054 error:

ORA-12054: 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.

Robert Freeman notes this on the ORA-12054 error (from the book Oracle Replication):

The quickest way to find out for sure is to use the dba_materialized view.explain_materialized view procedure. It will tell you why you are not getting a fast refreshable materialized view. This procedure most helpful when trying to build fast refresh materialized views.

Also, read the Oracle documentation about materialized views as there are a number of restrictions and requirements that you must meet in order for a view to be able to fast refresh. Note that the requirements are different for a locally built materialized view, as opposed to an materialized view built on a remote table.

There are restrictions on using the ON COMMIT refresh option in materialized views.  For complete explanations of the ORA-12054 issue, see the book "Oracle Replication".There are restrictions on using the ON COMMIT refresh option in materialized views. 

  • Include the primary key in the select clause of the materialized view

  • Make sure to create the materialized view log on the master table

  • To create a materialized view with the on commit refresh clause) you must have the on commit refresh object privilege on any master tables that you do not own or you must have the on commit refresh system privilege.

Oasma Mustafa recommends the following workaround for the ORA-12054 error:

1- I created a dummy table contain 1 row with 1 column only contains sysdate.

2- I created a job that this sysdata on daily basis.

3- I call this column in where condition of the materialized view.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.