In a materialized view with refresh fast on commit i have the below query select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID, pp.hierarchicode,pp.id, aup.userid from app_useracess aup, core_organization pp; when the app_useracess changes the materialize view change the data with fast method but when a commit occure on core_organization the materialize view does not refresh , unfortunately even i refresh it with this statement : DBMS_SNAPSHOT.REFRESH( 'CORE_POWER_AUTHORIZE_ALLOW','F'); it does not refresh , and when i refresh it completely with C as a secound parameter it sense the data changes . is it possible to refresh the materialize view with fast method and on commit on the core_organization table ?? update : the MV DDL is : create materialized view CORE_POWER_AUTHORIZE_ALLOW refresh fast on commit as select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,P.ROWID AS P_ROWID , pp.hierarchicode,pp.id, aup.userid,p.id as pID from core_power p ,app_userspower aup, core_power pp where p.id = aup.powerid and pp.hierarchicode like p.hierarchicode || '%'; i also use dbms_mview.explain_mview and the result is : REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML Y REFRESH_FAST_AFTER_ANY_DML Y REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view and there is so funny that the materialize refreshed base on this query : select e.LAST_REFRESH_TYPE,e.LAST_REFRESH_DATE from dba_mviews e where e.mview_name = 'MV_NAME'; but the count(*) from my MV does not changes .

I am receiving the following error while trying to create a materialized view with a geometry field on Oracle (21c):

ORA-12015: cannot create a fast refresh materialized view from a complex query

I will leave here some snippets to reproduce this error. First of all, we will need to create two simple tables and its logs:

CREATE TABLE signos.lookup_table (
    objectid NUMBER (38) NOT NULL,
    table_rowid rowid NOT NULL
);


CREATE TABLE signos.dirty_table (
    objectid NUMBER (38) NOT NULL,
    shape MDSYS.GEOMETRY
);

CREATE MATERIALIZED VIEW LOG ON signos.lookup_table WITH ROWID INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON signos.dirty_table WITH ROWID INCLUDING NEW VALUES;

This should work since we omitted the geometry field from the materialized view query:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.objectid,
    dt.rowid AS dt_rowid,
    lt.rowid AS lt_rowid
FROM
    signos.dirty_table dt,
    signos.lookup_table lt
WHERE
    lt.table_rowid (+) = dt.rowid
    AND lt.table_rowid IS NULL;

Unfortunately, we can't say the same about this one:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.objectid,
    dt.shape, -- !!!
    dt.rowid AS dt_rowid,
    lt.rowid AS lt_rowid
FROM
    signos.dirty_table dt,
    signos.lookup_table lt
WHERE
    lt.table_rowid (+) = dt.rowid
    AND lt.table_rowid IS NULL;

Which returns the aforementioned error:

ORA-12015: cannot create a fast refresh materialized view from a complex query

I am not sure if this error is expected, there isn't much documentation on this topic. The production version of these materialized views will have up to 8 million records so fast-refresh will come in handy to my use case. That said, any help will be very appreciated. **Edit 1** Thanks to @mustaccio for pointing out Oracle's documentation on the topic. I removed the join as recommended:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.rowid,
    dt.objectid,
    dt.shape
FROM
    signos.dirty_table dt;

And I got a different error:

ORA-30373: object data types are not supported in this context

TIA

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink. - The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink). - The table in the external system does not have a primary key. ----------- I'm a novice. Here's what I've tried: --in the external system: create materialized view log on external_system.workorder with rowid; --in the primary system: create materialized view primary_system.workorder_mv build immediate refresh fast start with sysdate next sysdate + (30/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, status, --other fields longitudex, latitudey from external_system.workorder@gistomax -------------------- When I try the above, I get a litany of errors, starting with one that says I need a primary key. I found a page that suggests that it's possible to do a fast refresh over a dblink . But the example uses a primary key, not a rowid. And the docs say : > Rowid materialized views are not eligible for fast refresh after a > master table reorganization until a complete refresh has been > performed. But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there. -------------------- **Question:** Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?

I have a couple of nested materialized views that fast refresh. They worked fine in Oracle 11g but since upgrading to Oracle 12c (12.1.0.2.0 on Solaris) I am occasionally getting duplicate rows (the rows are not identical, usually some data has changed - I suspect the duplicates reflect the before and after change state). If I subsequently do a complete refresh the duplicate rows disappear. I have My Oracle Support access but I haven't been able to find any bugs that I can tie to this behaviour. I am aware of new out-of-place refreshes that have been added to Oracle 12c. The documentation says of the out-of-place functionality (edited for brevity). > It helps to avoid potential problems such as ... intermediate refresh > results being seen. Are "**intermediate refresh results**" what I am seeing now? (I didn't before). Is anyone aware of any relevant issues/bugs/patches?

Under what conditions would a materialized view log not update? I must be missing something. I've got a 10gR2 database. I can create the following MV logs: CREATE MATERIALIZED VIEW LOG ON linked_t1 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON t1 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES; Then if I do an insert: INSERT INTO linked_t1 (id, link_id, link_analysis_id) VALUES ('11111111','22222222','0000000001'); I see my insert in the MV Log: SQL> select * from MLOG$_LINKED_T1 t; LINK_ID M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ -------- ------------------ ---------- ----------- --------- --------- --------------- 11111111 AAHvaaAAHAABvDsACR 11 1/1/4000 I N FE Not really sure what to make of the 1/1/4000 snaptime. As soon as I create a materialized view: CREATE MATERIALIZED VIEW mv$sub2 (c1, c2, m1, m2, m3, m4, m5) USING INDEX TABLESPACE idx REFRESH FAST WITH ROWID ON COMMIT DISABLE QUERY REWRITE AS SELECT la.rowid c1, ar.rowid c2, ar.analysis_id m1, ar.id m2, ar.valid m3, la.analysis_id m4, la.id m5 FROM linked_t1 la, t1 ar WHERE ar.analysis_id = la.analysis_id; The materialized view log on LINKED_T1 is emptied, and will no longer track any DML! Why is that? I have not made any changes to T1 for this test. I should mention that this MV was suggested by DBMS_ADVISOR.TUNE_MVIEW.