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 SEPTEMBER 27, 2023

Applies to:

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
/

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.