Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12053: this is not a valid nested materialized view (oracle,9.2.0.8,unix)
ORA-12053: this is not a valid nested materialized view [message #278197] Fri, 02 November 2007 06:16 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

i am getting error whne i am trying to create MATERIALIZED view based on anothe rMATERIALIZED view.

CREATE MATERIALIZED VIEW LOG ON cme.ERP_TK_BACKLOG_DAILY_MV
TABLESPACE TRANSD
WITH ROWID;



CREATE MATERIALIZED VIEW ERP_TKRRL_BACKLOG_DAILY_MV
TABLESPACE TRANSD
NOLOGGING
NOCACHE
NOPARALLEL
BUILD DEFERRED
USING INDEX TABLESPACE TRANSX
REFRESH FAST WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS SELECT * FROM  ERP_TK_BACKLOG_DAILY_MV;



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


please help me out this issue.

Thanks,
Sagar
Re: ORA-12053: this is not a valid nested materialized view [message #278200 is a reply to message #278197] Fri, 02 November 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12053: this is not a valid nested materialized view
 *Cause:  The list of objects in the FROM clause of the definition of this
          materialized view had some dependencies upon each other.
 *Action: Refer to the documentation to see which types of nesting are valid.

Regards
Michel
Re: ORA-12053: this is not a valid nested materialized view [message #278202 is a reply to message #278197] Fri, 02 November 2007 06:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The Oracle manual says

You can create a nested materialized view on materialized views, but all parent and base materialized views must contain joins or aggregates. If the defining queries for a materialized view do not contain joins or aggregates, it cannot be nested. All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log. All the underlying objects are treated as if they were tables. In addition, you can use all the existing options for materialized views.


Ross Leishman
Previous Topic: Fragmenting query
Next Topic: Problem with bulk collect limit
Goto Forum:
  


Current Time: Fri Dec 02 20:55:27 CST 2016

Total time taken to generate the page: 0.38131 seconds