ORA-12054 while creating Materialized View

Question

Now I am working with Materialized views and i try to create test materialized view:

  create table ma_user.test_mview
(
  col1 int,
  col2 int,
  date_col timestamp
)

insert all
  into ma_user.test_mview values (1,1,SYSDATE)
  into ma_user.test_mview values (1,2,SYSDATE + 10/1440)
  into ma_user.test_mview values (1,2,SYSDATE + 30/86400)
  into ma_user.test_mview values (2,1,SYSDATE + 1/48)
  into ma_user.test_mview values (2,2,SYSDATE + 1/24)
  into ma_user.test_mview values (2,2,SYSDATE + 1/72)
  into ma_user.test_mview values (2,3,SYSDATE + 1/96)
  SELECT * FROM dual;

  CREATE MATERIALIZED VIEW ma_user.test_mat_view1
BUILD IMMEDIATE
REFRESH
complete
ON COMMIT
as
  select col1,col2 from ma_user.test_mview
  where current_date > date_col;

but I get an error:

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

Maybe I should change ON COMMIT on ON_DEMAND?

XING XING · Accepted Answer · 2016-07-19T10:15:48

Since you are using current_date function in your query, its not able to issue work. Change it to ON DEMAND. It will work.

Also create a primary key for your table.

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some of the options may not be available.

ON COMMIT

Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

Working for me: -


CREATE MATERIALIZED VIEW test_mat_view1
BUILD IMMEDIATE
REFRESH COMPLETE
ON COMMIT
as
  select col1,col2 from test_mview
 -- where current_date > date_col
  ;

ORA-12054 while creating Materialized View

1 Answers