ORA-12013 : Error in MVIEWS [message #419196] |
Thu, 20 August 2009 06:06  |
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 #419202 is a reply to message #419200] |
Thu, 20 August 2009 06:24   |
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 #419219 is a reply to message #419207] |
Thu, 20 August 2009 09:11   |
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>
|
|
|
|
|