Materialized view restriction?

[Mike Killough]

I am trying to create a top layer materialized view that joins other 
materialized views and I am getting an ORA-12053: this is not a valid nested 
materialized view. I can't even use one of the MV's. Here is a simple 
example to illustrate the problem. Could someone please tell me what I'm 
missing?

QUESTD:scott> create materialized view log on emp
 2  with sequence, rowid (
 3  EMPNO,
 4  ENAME,
 5  JOB,
 6  MGR,
 7  HIREDATE,
 8  SAL,
 9  COMM,
10  DEPTNO)
11  including new values
12  /
Materialized view log created.

QUESTD:scott> create materialized view log on dept
 2  with sequence, rowid(
 3  DEPTNO,
 4  DNAME,
 5  LOC)
 6  including new values
 7  /
Materialized view log created.

QUESTD:scott> CREATE MATERIALIZED VIEW "EMP_DEPT_MVT"
 2    BUILD IMMEDIATE
 3    USING INDEX
 4    REFRESH FAST
 5    -- ON COMMIT
 6    WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
 7    ENABLE QUERY REWRITE
 8  as
 9  select a.rowid emprowid, b.rowid deptrowid, a.ename, b.loc
10    from emp a, dept b
11   where a.deptno = b.deptno
12  /
Materialized view created.

QUESTD:scott> create materialized view log on emp_dept_mvt
 2  with sequence, rowid(
 3  ENAME,
 4  LOC)
 5  including new values
 6  /
Materialized view log created.

QUESTD:scott>
QUESTD:scott> CREATE MATERIALIZED VIEW "EMP_DEPT_NEST_MVT"
 2    BUILD IMMEDIATE
 3    USING INDEX
 4    REFRESH FAST
 5    -- ON COMMIT
 6    WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
 7    ENABLE QUERY REWRITE
 8  as
 9  select a.ename
10    from emp_dept_mvt a
11  /
 from emp_dept_mvt a
      *
ERROR at line 10:
ORA-12053: this is not a valid nested materialized view
TIA,

Mike





_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mike Killough
 INET: [EMAIL PROTECTED]
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).