One one db, I have a table called xyz_rtab. The table structure doesn't really matter, it's just any table with a primary key. Then, I create a view on top of the table:
db2> create view xyz_v1 as select * from xyz_rtab;
View created.
Why do that? Well, my table is not really a table in my environment. The view on top of the table is really refactoring a business object that is used by several systems but whose time has come to change. But it doesn't matter, for the purposes of this demonstration.
Next, I change dbs and create the database link I'll use to get this data.
db1> create database link db2 using 'db2';
Database link created.
Now, I query across that dblink to verify that I can actually see the data:
db1> select count(*) from xyz_v1@db2;
COUNT(*)
----------
10
db1> select count(*) from xyz_rtab@db2;
COUNT(*)
----------
10
If I can query the data I should be able to create a mview, right? Lets try from the base table:
db1> create materialized view xyz_rtab_mview as select * from xyz_rtab@db2;
Materialized view created.
db1> exec dbms_mview.refresh(list=>'xyz_rtab_mview',method=>'C',atomic_refresh=>true);
PL/SQL procedure successfully completed.
No problem, that's what we expect. Now lets try from the view:
db1> create materialized view xyz_mv1 as select * from xyz_v1@db2;
create materialized view xyz_mv1 as select * from xyz_v1@db2
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1
Nice.