ORA-12053: this is not a valid nested materialized view

[Markus Kunze]

Hi all,

while trying to create a nested Materialized View (MVA2) on a MV (MVA1) I
get ORA-12053.
According to the Oracle8i Data Warehousing Guide this is because of using a
complex query. But is this a complex query? Anyone an idea what the problem
might be?
Thanks in advance for your help.

Markus

Here are the statements:

CREATE TABLE A (
SP_A VARCHAR2 (100),
SP_B NUMBER (38) NOT NULL,
SP_C NUMBER (38),
SP_D VARCHAR2 (100),
SP_E VARCHAR2 (100),
PRIMARY KEY ( SP_A ) ) ;

CREATE SNAPSHOT LOG ON A WITH ROWID;

CREATE MATERIALIZED VIEW MVA1
REFRESH FAST WITH ROWID
AS SELECT
SP_A
,ROWID RID
,SP_B
,SP_C
,SP_D
FROM A;

CREATE SNAPSHOT LOG ON MVA1 WITH ROWID;

CREATE MATERIALIZED VIEW MVA2
REFRESH FAST WITH ROWID
AS SELECT
SP_A XXX
,SP_B YYY
,ROWID ZZZ
FROM
MVA1;

[Markus Kunze]

It's 8.1.7.2

"Markus Kunze" <markus...@sdm.de> schrieb im Newsbeitrag
news:apb0qr$80p$1...@solti3.muc.sdm.de...

[Jusung Yang]

This appears to be a bug. ORALCE has some explanations to this. Still,
looks like a nested MV bug to me. It would work, if you put the master
and child MVs in separate schemas. If you really want them to be in
the same schema, create a loopback db link and attach it to MVA1 in
your MVA2 definition.

- Jusung Yang

"Markus Kunze" <markus...@sdm.de> wrote in message news:<apb36s$8pp$1...@solti3.muc.sdm.de>...