HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

Oracle 12c - Create materialized view results in ORA-00942

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
creatematerialized12cview00942oraresultsoracle

Problem

I have a view that I can access through a database link as such:

SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

COUNT(*)
--------
110


I can create and successfully query the remote view through a synonym:

SQL> create synonym REMOTE_VIEW for REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Synonym created

SQL> select count(*) from REMOTE_VIEW;

COUNT(*)
--------
110


If I try to create a materialized view using the synonym however, it fails:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_VIEW;

ORA-00942: table or view does not exist


Creating the materialized view without the synonym succeeds:

SQL> create materialized view REMOTE_MV
build immediate
refresh complete
next sysdate+1
with ROWID
as
select * from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;

Materialized view created


Any ideas why using the synonym fails? Ideally I would like to use the synonym to insulate the materialized view from the database link. The database link could differ based on environment and I want to ensure the materialized view query remains the same regardless.

Solution

Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.
More

You cannot create Materialized View from Synonym. I don't know why would you like to that but we create materialized view as your working example.

Context

StackExchange Database Administrators Q#153541, answer score: 2

Revisions (0)

No revisions yet.