Home » Server Options » Replication » Materialized view problem on a remote view (dblink)
Materialized view problem on a remote view (dblink) [message #139989] Fri, 30 September 2005 07:39
denis.flotat
Messages: 13
Registered: September 2005
Junior Member
Hi

I have defined views on a database x.
I would like to use the SNAPSHOT/MVIEWS mechanism in order to get a local copy of those views on another database y.

When i work locally without using a dblink i can create a snapshot like this :

create snapshot test as select * from v_myView;

The problem comes when i want to build a snapshot on a remote view :

create snapshot test as select * from v_myView@DBLINK
ERROR on line :
ORA-00942: Table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1

=> this is the result on 10g R2 (windows).
On previous database release (9.2), an ORA-600 error was produced. It seems that snapshot are working correctly when accessing directly to the tables not the view.
---------------------------
Here is a complete test that shows the problem :

SQL> Connect system/oracle@SDD
SQL> create table test as select * from tabs;
Table created.
SQL> create view v_test as select * from system.test;
View created.
SQL> create public database link dbl_test connect to system identified by oracle using 'SDD';
Database link created.
SQL> create materialized view mv_test as select * from v_test@dbl_test;
create materialized view mv_test as select * from v_test@dbl_test
*
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

Someone know how to solve this ? I guess it is a real Oracle Bug.

Thanx in advance.
Previous Topic: ora-04020: deadlock detected while trying to lock objext XX.MLOG$_YY_@MyDBLINK.US.ORACLE.COM
Next Topic: Data Replication from DB2 to Oracle 10g
Goto Forum:
  


Current Time: Fri Mar 29 02:44:30 CDT 2024