Oracle 12c – Create Materialized View Results in ORA-00942

dblinkmaterialized-vieworaclesynonyms

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.

Best Answer

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.

Related Solutions

Materialized view for the last 24 hour period

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.

Do you need a materialized view? Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)? That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.

Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?

Create FAST Materialised View in Oracle 10g with ROWID

Ok, so 2 problems here:

ORA-29701 this may be caused by a bug, permission issue with the executables or other environmental errors, such as deleting socket files from the tmp directory. First investigate this.
ORA-12052: cannot fast refresh materialized view. For this:

Diagnosing ORA-12004 Refresh Fast Cannot be Used (Doc ID 179469.1)

1.8 Materialized View References Synonyms or Views

Fast refreshable materialized views can be created based on master tables and master materialized views only. Materialized views based on a synonym or a view must be complete refreshed. This actually is a consequence of the cause discussed in Section 1.3. From 8.1.6 onwards, use of a synonym or a view in place of a direct reference to a master table renders the materialized view complex, and thus it cannot be fast refreshed. This can be seen if a direct attempt is made to create the materialized view as fast refreshable when it references synonyms or views.

But instead of manually searching for reasons why fast refresh is not possible, let the database do the work:

Run $ORACLE_HOME/rdbms/admin/utlxmv.sql to create the MV_CAPABILITIES_TABLE.

Use DBMS_MVIEW.EXPLAIN_MVIEW to explain the mview:

begin
  DBMS_MVIEW.EXPLAIN_MVIEW ('create materialized view ....');
end;
/

After that you can find the reason why fast refresh is not possible in MV_CAPABILITIES_TABLE.

Related Question