Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Materialized View: Refresh on COMMIT restrictions

AnnelizeFNov 25 2019 — edited Nov 26 2019

Hi

We're running Oracle 12.1 in the cloud.

I am attempting to create a materialized view as follows:

CREATE MATERIALIZED VIEW my_schema.mv_mat_view

BUILD IMMEDIATE

REFRESH COMPLETE ON COMMIT

DISABLE QUERY REWRITE

AS

    SELECT * FROM my_schema.v_my_view;

and I'm getting the following error. ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

I can see from the documentation there are quite a few restrictions on FAST refresh which my select statement won't comply with and is the reason that I'm doing a COMPLETE refresh.

However from more research I can only find two reasons why a ON COMMIT won't be possible:

The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,

•ON COMMIT clause is not supported for materialized views containing object types.

•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.

None of these restrictions apply to my_schema.v_my_view. However, my v_my_view does consist of multiple WITH statements. Are there any other restrictions that I should be aware of?

Executing the following only gives me restrictions on why my MV isn't eligible for FAST refresh, but no information on ON COMMIT

BEGIN

   DBMS_MVIEW.explain_mview ('mv_mat_view');

END;

Thanks

Annelize

This post has been answered by Paulzip on Nov 25 2019
Jump to Answer
Comments
Post Details
Added on Nov 25 2019
18 comments
9,745 views