3

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.

2

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.

1

Managed to figure it out! You can create materialized views on synonyms, however it was my lack of understanding edition-based redefinition (EBR) that was causing the issues.

Private synonyms by default are EDITIONABLE:

CREATE SYNONYM

[ EDITIONABLE | NONEDITIONABLE ]

Use these clauses to specify whether the synonym is an editioned or noneditioned object if editioning is enabled for the schema object type SYNONYM in schema. For private synonyms, the default is EDITIONABLE. For public synonyms, the default is NONEDITIONABLE.

Materialized views however are noneditioned objects:

Using Edition-Based Redefinition

24.1.1.2.1 Materialized Views

A materialized view is a noneditioned object that can specify an evaluation edition, thereby enabling it to depend on editioned objects.

CREATE MATERIALIZED VIEW [ schema.] materialized_view other_clauses [ evaluation_edition_clause ] [ query_rewrite_clause ] AS subquery

Where evaluation_edition_clause is:

EVALUATE USING { CURRENT EDITION | EDITION edition | NULL EDITION }

CURRENT EDITION is the edition in which the DDL statement runs. Specifying NULL EDITION is equivalent to omitting the clause that includes it. If you omit evaluation_edition_clause, then editioned objects are invisible during name resolution.

So I successfully tested two options to solve the problem:

  • Option 1 - make the synonym NONEDITIONABLE, in which case it will be visible to the materialized view OR
  • Option 2 - Specify the evaluation_edition_clause on the materialized view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.