Creating materialized view of another user via private db link succeeded by selecting single table while failed by selecting more than one table

(Doc ID 2305974.1)

Last updated on OCTOBER 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
Creating materialized view of another user via private db link succeeded by selecting single table while failed by selecting more than one table

Symptoms

User U1 create materialized view for U2 by selecting single table succeeded, while creating materialized view for U2 by selecting more than one table failed.

This is the test result:

CONNECT / as sysdba

SELECT *
FROM V$VERSION
/

DROP USER U1 CASCADE
/

DROP USER U2 CASCADE
/

CREATE USER U1
IDENTIFIED BY U1
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS
/

GRANT DBA
TO U1
/

CREATE USER U2
IDENTIFIED BY U2
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS
/

GRANT CREATE SESSION,
CREATE DATABASE LINK,
CREATE TABLE,
CREATE SYNONYM
TO U2
/

CONNECT U2/U2
CREATE DATABASE LINK U2_ORCL
CONNECT TO scott
IDENTIFIED BY tiger
USING 'o11gr2'
/

CREATE SYNONYM EMP
FOR EMP@U2_ORCL
/

CREATE SYNONYM DEPT
FOR DEPT@U2_ORCL
/
CONNECT U1/U1

-- No issues creating MV of U2 by U1, MV is referencing single remote
table

CREATE MATERIALIZED VIEW U2.EMP_MV
AS
SELECT ENAME
FROM EMP
/
CREATE MATERIALIZED VIEW U2.DEPT_MV
AS
SELECT DNAME
FROM DEPT@U2_ORCL
/

-- ORA-02019 when creating MV of U2 by U1 , when MV is referencing more than one remote table

CREATE MATERIALIZED VIEW U2.EMP_DEPT_MV
AS
SELECT ENAME,
DNAME
FROM EMP,
DEPT
/

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