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