This discussion is archived
0 Replies Latest reply: Aug 21, 2012 7:58 AM by Blemon RSS

Cannot use fast refresh on materialized view

Blemon Newbie
Currently Being Moderated
Hi all,

I've hit a problem when trying to create a fast-refreshable materialized view.

I've got two databases, one 10.2.0.10, another 11.2.0.1.0, running on 32-bit Windows. Both are enterprise edition, and I'm trying to pull data from the 10g one into the 11g one. I can happily query across the database link from 11g to 10g, and can use complete refresh with no problem except the time it takes.

On the 10g side, I've got tables with primary keys and m.v. logs created, the logs being of this form ...

CREATE MATERIALIZED VIEW LOG ON table WITH PRIMARY KEY INCLUDING NEW VALUES

On the 11g side, when I try to create an m.v. against that ...

CREATE MATERIALIZED VIEW mv_table REFRESH FAST WITH PRIMARY KEY AS SELECT col1, col2 FROM table@dblink;

... I get an ORA-12028 error (materialized view type is not supported by master site).

After running the EXPLAIN_MVIEW procedure it shows this;

REFRESH_FAST_AFTER_INSERT not supported for this type mv by Oracle version at master site

A colleague has managed to build a fast-refresh m.v. from the same source database, but pulling to a different one than I'm using; his target is also 10g, like the (common) source, so I've no idea why I'm getting the 'not supported' message whilst he isn't.

I've been able, on previous projects, to do exactly what I'm trying to achieve but on those someone with more knowledge than me has configured the database!

I'm now stumped. I'm also no DBA but despite that it's been left to me to install the new 11g database on the 32-bit Windows server from scratch, so there are probably a couple of things I'm missing. It's probably something really obvious but I don't really know where to look now.

If anyone can give me any pointers at all, I'd be really grateful.

Thanks in advance,
Steve

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points