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:
CREATE MATERIALIZED VIEW abc
REFRESH FAST ON COMMIT
AS SELECT * FROM abc@PRI;
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.
and include the URL for the page.
