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;

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

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.

SELECT * FROM  ABC WHERE  TMSTP> SYSDATE-1;

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> 

SELECT OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME='ABC'

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

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

SQL>