Table not found when creating MView

[Exie]

I created a DB link to another server, and developed an SQL which queries a view through the DB Link.

This works ok, but when I try to create a materialized view with the query, I always get:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1

The statement looks like this:

CREATE MATERIALIZED VIEW DWH.MERGESALESFIGURES_JS_MV 
TABLESPACE DWHTAB
CACHE
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES 1 )
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('19-Nov-2008 23:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT sysdate + 1   
AS 
SELECT 
    Customer_Id,
    Customer_Name,
    Parent_Cust_Name,
 <snip>
    parent_id,
    acctmgr_id,
    acctmgr
from mtms.meregesalesfigures_js_v@PROD_DB;

As i said .. I can run the SQL.. the DB link is created like so:

create public database link "PROD_DB"
connect to MTMS
identified by "blah"
using 'blah';

 

[SantaMufasa]

So, Exie, you are saying that as the Oracle user that is attempting to create the materialized view, you can successfully run this query:

SELECT 
    Customer_Id,
    Customer_Name,
    Parent_Cust_Name,
 <snip>
    parent_id,
    acctmgr_id,
    acctmgr
from mtms.meregesalesfigures_js_v@PROD_DB;

Please confirm.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Exie]

Yep, the SQL runs fine as user "dwh" or "sys" ...

I think I might have found the problem though... the SQL query was extract data across the DB Link from a view I created in the remote DB.

I sucked the guts out of the view definition, and put that into the MView query so that it runs against the raw tables in the remote DB and it works cool now.

Apart from wanting a bit of abstraction, I was concerned about weather the processing (eg. use of TRIM and SUBSTR etc) would be passed through to the remote DB.

It looks like it has passed all the processing work over which is good as the remote DB has ALOT more horsepower.

So in a nut shell, it now works.