My Oracle Support Banner

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:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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
/

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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