Home » Server Options » Replication » ORA-12013 : Error in MVIEWS (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit)
ORA-12013 : Error in MVIEWS [message #419196] Thu, 20 August 2009 06:06 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,


CREATE MATERIALIZED VIEW LOG ON ABC;

CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST 
START WITH SYSDATE
NEXT  SYSDATE+20/(24*60)  
FOR UPDATE 
AS
SELECT * FROM  ABC WHERE  TMSTP> SYSDATE-1;



It is working fine.

Appended the Query with WHERE clause in MV_ABC MVIEWS.



DROP MATERIALIZED VIEW LOG ON ABC;

DROP MATERIALIZED VIEW MV_ABC;


CREATE MATERIALIZED VIEW LOG ON ABC;

CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST 
START WITH SYSDATE
NEXT  SYSDATE+20/(24*60)  
FOR UPDATE 
AS
SELECT * FROM  ABC WHERE  TMSTP> SYSDATE-1;

ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh






Pls. suggest.
Re: ORA-12013 : Error in MVIEWS [message #419200 is a reply to message #419196] Thu, 20 August 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12013: updatable materialized views must be simple enough to do fast refresh
 *Cause:  The updatable materialized view query contained a join, subquery,
          union, connect by, order by, or group by caluse.
 *Action: Make the materialized view simpler. If a join is really needed,
          make multiple simple materialized views then put a view on top of
          them.

Regards
Michel
Re: ORA-12013 : Error in MVIEWS [message #419202 is a reply to message #419200] Thu, 20 August 2009 06:24 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,

Using below query for MVIEWS, which is simple
and query does'nt contained a join, subquery,union, connect by, order by, or group by caluse.

SELECT * FROM  ABC WHERE  TMSTP> SYSDATE-1;


Pls. Suggest.
Re: ORA-12013 : Error in MVIEWS [message #419207 is a reply to message #419202] Thu, 20 August 2009 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is that you posted twice the same thing for both what worked and what did not.

Don't just explain copy and paste your SQL*Plus session.

Regards
Michel
Re: ORA-12013 : Error in MVIEWS [message #419219 is a reply to message #419207] Thu, 20 August 2009 09:11 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,

Excuse me.
As I have posted the first and Second Query as same in MVIEWS Definition.

Pls. find the SQL*Plus session Pasted output and seek your uggestion.


SQL> CREATE MATERIALIZED VIEW LOG ON ABC;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_ABC
  2  REFRESH FAST 
  3  START WITH SYSDATE
  4  NEXT  SYSDATE+20/(24*60)  
  5  FOR UPDATE 
  6  AS SELECT * FROM  ABC;

Materialized view created.

SQL> DROP MATERIALIZED VIEW MV_ABC;

Materialized view dropped.

SQL> DROP MATERIALIZED VIEW LOG ON ABC;

Materialized view log dropped.

SQL> CREATE MATERIALIZED VIEW LOG ON ABC;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_ABC
  2  REFRESH FAST 
  3  START WITH SYSDATE
  4  NEXT  SYSDATE+20/(24*60)  
  5  FOR UPDATE 
  6  AS SELECT * FROM  ABC WHERE  TMSTP > SYSDATE-1;
AS SELECT * FROM  ABC WHERE  TMSTP > SYSDATE-1
                  *
ERROR at line 6:
ORA-12013: updatable materialized views must be simple enough to do fast
refresh


SQL> 

Re: ORA-12013 : Error in MVIEWS [message #419223 is a reply to message #419219] Thu, 20 August 2009 09:51 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

SELECT OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME='ABC'

??

[Updated on: Thu, 20 August 2009 09:51]

Report message to a moderator

Re: ORA-12013 : Error in MVIEWS [message #419235 is a reply to message #419223] Thu, 20 August 2009 10:57 Go to previous message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentle Babu,


SQL> SELECT OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME='ABC';

OBJECT_TYPE
------------------
TABLE

SQL> 

Previous Topic: Selective incremental replication between two instances?
Next Topic: Complete Transaction Statistics of MVIEWS on Base Table
Goto Forum:
  


Current Time: Thu Mar 28 07:41:44 CDT 2024