Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    2

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

    I am trying to create a materialized view that reads from another materialized view. And i need both to be Fast refreshed. But i get this error when creating the second MV. If i create as FORCE no problem, but cannot be FAST:

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

    Anyone knows why??

    sql:
    *****************

    CREATE TABLE OFFICE70 (
    OFFICEID VARCHAR2(32) NOT NULL,
    KODEKANTAH VARCHAR2(4) NOT NULL,
    NAMAKANTAH VARCHAR2(50));
    ALTER TABLE OFFICE70 ADD CONSTRAINT PK_OFFICE70 PRIMARY KEY (OFFICEID);
    CREATE MATERIALIZED VIEW LOG ON OFFICE70 NOCACHE WITH PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
    CREATE MATERIALIZED VIEW OFFICE71 TABLESPACE KANWIL USING INDEX TABLESPACE KANWIL REFRESH FAST ON DEMAND
    AS SELECT OFFICEID, KODEKANTAH, NAMAKANTAH FROM OFFICE70;
    CREATE MATERIALIZED VIEW LOG ON OFFICE71 NOCACHE WITH PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
    CREATE MATERIALIZED VIEW OFFICE72 TABLESPACE KANWIL USING INDEX TABLESPACE KANWIL REFRESH FAST ON DEMAND
    AS SELECT OFFICEID, KODEKANTAH, NAMAKANTAH FROM OFFICE71;
    *****************
    ORA-12053: this is not a valid nested materialized view

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As far as I know, you can't have a MVIEW log on an MVIEW. Create BOTH MVIEW as referencing office70 and it should work correctly, A log can be used by more them one MVIEW.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2007
    Posts
    2

    Ora-12053

    Thanks, for the answer, but i need these two levels of Materialized views due the first level are in different location, the first jump is done through a slowly WLAN, and need these two levels because my architecture.

    The point is I have created the materialized view and the materialized view log over this MV and no problem about that. The problem is when i try to create the second MV over the first MV with fast refresh, i got the error. If I create as Force refresh, not problem, and i can refresh them with complete refresh.

    Any idea?
    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •