While creating a materialized view in a 10.2.0.4 database using a view in a remote 10.2.0.3 database as the base table, the creation failed:
SQL> create materialized view db2_dba_ts_quotas_mv
build immediate
refresh with rowid complete on demand
as select tablespace_name, username, max_bytes
from dba_ts_quotas@db1;
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at “SYS.DBMS_SNAPSHOT_UTL”, line 1543
ORA-06512: at line 1
The user used in the database link has SELECT ANY DICTIONARY access in the remote database.
Creation of the same materialized views for other 10.2.0.4 database works. To address this issue, instead of creating materialized view based on sys.dba_ts_quotas, I created a view for sys.dba_ts_quotas for the db link user, and create the materialized view based on this view. It worked fine!
It is know bug in 10.2.0.3 . These Bugs are fixed in the 10.2.0.4 Patch Set
By: Raghavan on March 13, 2009
at 1:14 pm
Raghavan – thanks for the info and for visiting. Regards – Nico
By: nkristanto on March 16, 2009
at 3:39 am
Bonjour!
try this :
SQL> create materialized view db2_dba_ts_quotas_mv
build immediate
refresh with rowid complete on demand
as select * from (select tablespace_name, username, max_bytes
from dba_ts_quotas@db1);
By: Moez BOUCHRIHA on November 25, 2011
at 10:08 am