Prev: Connections which go bad
Next: Discrepancy between Top 5 wait events and object statistics
From: The Magnet on 12 Jan 2010 14:42
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"@NIVA.COM;
From: Mladen Gogala on 12 Jan 2010 15:31
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"@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
From: The Magnet on 12 Jan 2010 15:35
On Jan 12, 2:31 pm, Mladen Gogala <n...(a)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"@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"@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?
From: Shakespeare on 12 Jan 2010 15:44
Op 12-1-2010 20:42, The Magnet schreef:
> 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"@NIVA.COM;

1) Did you include all primary key columns of the table (widgets)
2) Alias the rowid (select ROWID as a_rowid). The stament you put in
here would generate an error.
3) How can you run the first statement without a table widget.widgets? A
materialized view log must be created on a TABLE.
4) You can't create a mv with the same name as a table. You should get
an ORA-955

Conclusion: these can't be the statements you actually ran.

Shakespeare
From: Maxim Demenko on 12 Jan 2010 16:24
On 12.01.2010 20:42, 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"@NIVA.COM;

According to Note ID 301627.1 on commit with master tables on remote
database is not supported. Maybe in newer releases things have changed,
but on 10.2.0.4 it still seems to be the no way around.

Best regards

Maxim
 |  Next  |  Last
Pages: 1 2
Prev: Connections which go bad
Next: Discrepancy between Top 5 wait events and object statistics