Oracle-L: Materialized view restriction?

From: Mike Killough <mwkillough_at_hotmail.com>
Date: Mon, 10 Mar 2003 09:04:35 -0800
Message-ID: <F001.00564ABE.20030310090435@fatcity.com>

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: mwkillough_at_hotmail.com

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: ListGuru_at_fatcity.com (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). Received on Mon Mar 10 2003 - 11:04:35 CST