Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, %D0%92%D0%B5%D1%80%D0%BE%D0%BD%D0%B8%D0%BA%D0%B0.

Asked: February 05, 2018 - 1:48 pm UTC

Last updated: February 07, 2018 - 11:21 am UTC

Version: 11

Viewed 1000+ times

You Asked

Good day.
I need help with refresh materialized view.
I created synonym MySynonym:
CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";


I created materialized view:
CREATE MATERIALIZED VIEW MyMView TABLESPACE My_DATA NOLOGGING BUILD DEFERRED USING INDEX TABLESPACE MY_INDEXES REFRESH FAST WITH PRIMARY KEY AS Select field1, field2 from MySynonym;


After complete refresh of MyMView, fast refresh rises error - ORA-12052: cannot fast refresh materialized view

Materialized view log exists for SomeTable in MyScheme2.
SomeTable has clob field - field3(this field does not include in MyMView).

and Chris said...

You can create a fast refresh materialized view on a synonym and with a table with a clob:

create table t (
  x int primary key,
  y clob
);

create materialized view log on t with primary key;

create or replace synonym syn for t;

create materialized view mv 
refresh fast as
  select * from syn;
  
select count(*) from mv;

COUNT(*)   
         0


So either:

- The materialized view log is missing or incorrectly defined for your query
- Your query doesn't support fast refresh

To figure out why it's not working in your case, use dbms_mview.explain_mview. The following describes how to do this:

http://www.orafaq.com/node/831

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

%D0%92%D0%B5%D1%80%D0%BE%D0%BD%D0%B8%D0%BA%D0%B0 %D0%A2%D1%8E%D1%80%D0%B8%D0%BD%D0%B0, February 06, 2018 - 2:26 pm UTC

Do I need recreate materialized view, if I will recreate synonym with the same(old) name?
Chris Saxon
February 06, 2018 - 4:19 pm UTC

What do you mean? Run "create or replace synonym..."?

What happens to your MV when you recreate the synonym?

May need to rebuild MV

Pete, February 07, 2018 - 12:48 am UTC

My past experience has been that materialized view and synonym dependencies are based on the object id in the data dictionary, not the object name, so if you rebuild a source table, view, or synonym, you have no choice to rebuild the materialized view as well even if everything has the same names.
Chris Saxon
February 07, 2018 - 10:39 am UTC

That's likely yes. It depends on what exactly they're doing though...

%D0%92%D0%B5%D1%80%D0%BE%D0%BD%D0%B8%D0%BA%D0%B0 %D0%A2%D1%8E%D1%80%D0%B8%D0%BD%D0%B0, February 07, 2018 - 8:27 am UTC

What do you mean? Run "create or replace synonym..."?
Yes.
For example:
at first I created synonym "MySynonym"
CREATE OR REPLACE SYNONYM "LOCAL_SCHEMA"."MySynonym" FOR "OTHER_SCHEMA"."MyTable";

then I will recreate synonym "MySynonym"
CREATE OR REPLACE SYNONYM "LOCAL_SCHEMA"."MySynonym" FOR "MyTable"@"MyDBLink";


What happens to your MV when you recreate the synonym?
I do not know, what will happen with MV.

Chris Saxon
February 07, 2018 - 10:38 am UTC

Have you tried running the "create or replace synonym..."? What happened?

%D0%92%D0%B5%D1%80%D0%BE%D0%BD%D0%B8%D0%BA%D0%B0 %D0%A2%D1%8E%D1%80%D0%B8%D0%BD%D0%B0, February 07, 2018 - 11:06 am UTC

MView can not be fast refreshed after that.
Chris Saxon
February 07, 2018 - 11:21 am UTC

Then you'll have to re-create the MV!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.