Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gopikrishnan.

Asked: September 13, 2018 - 3:37 pm UTC

Last updated: April 07, 2022 - 9:41 am UTC

Version: oracle 11g

Viewed 10K+ times! This question is

You Asked


Error starting at line : 12 in command -
CREATE MATERIALIZED VIEW EMP_MV
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS SELECT EMPID,EMPNAME FROM EMP


Error report -


ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "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.

and Connor said...

Well this seems fairly self-explanatory

"The materialized view did not satisfy conditions for refresh at commit time"

Most likely...you're missing a primary key

SQL> create table emp as select * from scott.emp;

Table created.

SQL>
SQL> create materialized view emp_mv
  2  build immediate
  3  refresh force
  4  on commit
  5  as select empno, ename from emp;
as select empno, ename from emp
                            *
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


SQL>
SQL> alter table emp add primary key ( empno );

Table altered.

SQL> create materialized view emp_mv
  2  build immediate
  3  refresh force
  4  on commit
  5  as select empno, ename from emp;

Materialized view created.




Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Materialised view log

Gopikrishnan Samyappan, September 14, 2018 - 9:59 am UTC

Thanks for this help!

Can you please explain me when the materialized log must be used ?

If suppose i have two schemas schemaA and schemaB. Creating materialized view using REFRESH FAST on SchemaB based on master table in schemaA required materialized log in SchemaA?

Regards,
Gopikrishnan
Connor McDonald
September 16, 2018 - 2:59 am UTC

The need for a materialized log, and what it contains, depends mainly on the SQL and refresh type that will be used to refresh the materialized view.

But we document all the usage models, and restrictions here:

https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007299

Also, search this site for DBMS_MVIEW.EXPLAIN_MVIEW for examples on how to test your mviews for refresh compatibility.

MV

Gopi Krishnan Samyappan, September 23, 2018 - 5:30 pm UTC

Can you explain why we opt using index in creating materialized view? example please if any
Connor McDonald
September 26, 2018 - 12:41 am UTC

Can you explain why we opt using index in creating materialized view?

You need to clarify what you're asking here

Helpful

Rameshwar Rdevkule, April 07, 2022 - 8:49 am UTC

actually we stuck with same problem but now problem solve
we use same method which you used.
Thanks once again...
Our problem below--->

SQL> create materialized view viwww_mat_ref_with refresh force on commit as select* from worker1 where salary=500000;
create materialized view viwww_mat_ref_with refresh force on commit as select* from worker1 where salary=500000
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


SOLUTION---->

SQL> create materialized view vi_mat_reff refresh force on commit as select* from worker1 where salary=500000;

Materialized view created.



Chris Saxon
April 07, 2022 - 9:41 am UTC

Thanks for sharing
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.