Re: Materialized Views

From: The Magnet <art_at_unsu.com>
Date: Tue, 12 Jan 2010 12:35:52 -0800 (PST)
Message-ID: <a3fc7d3b-054e-453e-8c38-3ee1ba2eb23c_at_s31g2000yqs.googlegroups.com>



On Jan 12, 2:31 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 12 Jan 2010 11:42:54 -0800, The Magnet wrote:
> > I'm getting an error when I try an do a fast refresh on a materialized
> > view.  The base table is very simple, 3 columns.  But, it exists over a
> > database link.  Like everyone else I am receiving a ORA-12054.  I've had
> > no luck with tryong everything out with all the example definitions.
> > This is one table with a primary key, I do not know what could be more
> > simple.  I've pasted my definitions below.
>
> > BTW:  I tried both INCLUDING and EXCLUDING on the log:
>
> > CREATE MATERIALIZED VIEW LOG ON WIDGET.WIDGETS WITH PRIMARY KEY
> > INCLUDING NEW VALUES;
>
> > CREATE MATERIALIZED VIEW WIDGET.WIDGETS REFRESH FAST ON COMMIT
> > AS
> > SELECT ROWID,
> >    "WIDGET_ID",
> >    "WIDGET_NAME",
> >    "WIDGET_OPTIONS"
> > FROM "WIDGETS"_at_NIVA.COM;
>
> Have you actually read what is the error 12054? Here it is:
>
> oerr ora 12054
> 12054, 00000, "cannot set the ON COMMIT refresh attribute for the
> materialized view"
> // *Cause:  The materialized view did not satisfy conditions for refresh
> at
> //          commit time.
> // *Action: Specify only valid options.
> //
>
> The problem is, of course, in the "rowid" selection.
>
> --http://mgogala.byethost5.com

I had also tried without ROWID and received the same error:

CREATE MATERIALIZED VIEW WIDGET.WIDGETS
REFRESH FAST ON COMMIT
AS
SELECT "WIDGET_ID",
   "WIDGET_NAME",
   "WIDGET_OPTIONS"
FROM "WIDGETS"_at_NIVA.COM;

ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Yes, I know what the error is. But what conditions? How simple can it get? A select statement with no joins, no referential constraints, a primary key, no indexes. It is only over a DB link. So, what is so complex? Received on Tue Jan 12 2010 - 14:35:52 CST

Original text of this message