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.
"Markus Kunze" <markus.kunze_at_sdm.de> wrote in message news:<apb36s$8pp$1_at_solti3.muc.sdm.de>...
> It's 8.1.7.2
>
>
> "Markus Kunze" <markus.kunze_at_sdm.de> schrieb im Newsbeitrag
> news:apb0qr$80p$1_at_solti3.muc.sdm.de...
> > 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;
> >
> >
Received on Fri Oct 25 2002 - 14:16:06 CDT
