Materialized view log of other schema

Question

I have two tables in two different schemas SCHEME A (tbl1) SCHEME B (tbl2)

I created a materialized view that joins the two tables.

I would like to fast update and for that reason I have created two logs (one per table) using the rowi(ORA-12015 Cannot create a fast refresh materialized view from a complex query)

When I create the view it tells me it's not possible as the query is complex. Actually the problem is the schema. Indeed the same query works if I create the tbl2 under the schema a. How to fix?

Make sure the schema that owns the materialized view also has select privileges on all source tables *and* materialized view logs. — pmdba, Apr 04 '23 at 21:47
Run DBMS_MVIEW.EXPLAIN_MVIEW to get more diagnostics about what is missing — Connor McDonald, Apr 05 '23 at 14:04
How are the grants to the table and log in Schema B given? Are they direct object-level grants made to Schema A, or are they made through a role? — pmdba, Apr 05 '23 at 17:09

score 0 · Answer 1 · answered Apr 15 '23 at 18:07

0

Instead of using the classic Joins, it is sufficient to insert all the tables in the from and the join constraints in the where of the query

answered Apr 15 '23 at 18:07

ciro

771
1
8
30

Materialized view log of other schema

1 Answers1