Create materialized view on a remote view fails with error ORA-942 (Doc ID 444089.1)

Last updated on MAY 13, 2013

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
Add ***Checked for relevance on 05-Feb-2010***

Symptoms

A user in local database is trying to create a materialized view on a view in a remote database. The link does not connect to the owner of the view directly. Instead it connects to a different user which has select privilege on the view.

The refresh/Create comes back with the following:

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

The 10046 Trace on the mview side shows the following statement failing:

begin
sys.dbms_snapshot_utl.get_log_name@<dblink>:master, :mowner, :lognm, :chkpk,:status, :objid);
end;

If we also trace the remote side we can see something similar to this:

RPC CALL:PROCEDURE SYS.DBMS_SNAPSHOT_UTL.GET_LOG_NAME
(MASTER IN VARCHAR2,MOWNER IN VARCHAR2, LOGNM OUT VARCHAR2, CHKFLG IN BINARY_INTEGER, STATUS OUT BINARY_INTEGER, MASOBJID OUT BINARY_INTEGER);
RPC BINDS:
bind 0: dty=1 bfp=ffffffff7b28e050 flg=08 avl=05 mxl=32 val="VIEW1"
bind 1: dty=1 bfp=ffffffff7b28e0a0 flg=08 avl=06 mxl=32 val="USER_B"
bind 2: dty=1 bfp=ffffffff7b28e0f0 flg=0a avl=00 mxl=30 val=""
bind 3: dty=3 bfp=ffffffff7b28e130 flg=00 avl=04 mxl=04 val=02
bind 4: dty=3 bfp=ffffffff7b28e158 flg=02 avl=04 mxl=04 val=00
bind 5: dty=3 bfp=ffffffff7b28e180 flg=02 avl=04 mxl=04 val=00


The issue is not there in 9i. Problem reported in 10g

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms