snippetMinor
Oracle 12c - Create materialized view results in ORA-00942
Viewed 0 times
creatematerialized12cview00942oraresultsoracle
Problem
I have a view that I can access through a database link as such:
I can create and successfully query the remote view through a synonym:
If I try to create a materialized view using the synonym however, it fails:
Creating the materialized view without the synonym succeeds:
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.
SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB;
COUNT(*)
--------
110I 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(*)
--------
110If 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 existCreating 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 createdAny 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.
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.