Blog Aggregator - Amardeep Sidhu

While working on another problem, I found this interesting little tidbit with Materialized Views querying views on Oracle 10.2.0.3.

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 existORA-06512: at “SYS.DBMS_SNAPSHOT_UTL”, line 960ORA-06512: at line 1

Nice.