oracle aide

September 19, 2007

“Create materialized view” fails on a remote view.

Filed under: fun, replication — oracleaide @ 4:22 pm

An attempt to create a materialized view fails if the master object is a remote view.
The error is ORA-00942, table or view does not exist.
This happens when the remote view is not owned by the database link user. In my case:

create database link demo_db_link connect to MVADMIN identified by MVADMIN

create materialized view mv_scott_emp as 
  select * from scott.emp@demo_db_link

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1

Metalink helps. But the news are not good: this is a bug in 10.2.0 (bug 5015547).
The fix is very simple and easy – upgrade to 11.1.
They are suggesting a workaround – use database links connecting directly to the view owner.
Something like:

create database link scott_db_link connect to scott identified by tiger

So far – this didn’t work for me.
What worked – a ROWID mview. Even with the original db link.

create materialized view mv_scott_emp REFRESH COMPLETE WITH ROWID as 
  select * from scott.emp@demo_db_link



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: