ORA-30353,ORA-32401

 

 

SQL> create materialized view pppp_mview
  2  refresh fast with rowid on demand start with sysdate next sysdate+1
  3  enable query rewrite
  4  as
  5  select count(*) from pppp
  6  ;
 
create materialized view pppp_mview
refresh fast with rowid on demand start with sysdate next sysdate+1
enable query rewrite
as
select count(*) from pppp
 
ORA-30353: expression not supported for query rewrite
 
SQL>
SQL>
SQL>
SQL> create materialized view pppp_mview
  2  refresh fast with rowid on commit
  3  enable query rewrite
  4  as
  5  select count(*) from pppp
  6  ;
 
create materialized view pppp_mview
refresh fast with rowid on commit
enable query rewrite
as
select count(*) from pppp
 
ORA-32401: materialized view log on "PPZHU"."PPPP" does not have new values
 
SQL> drop materialized view log on pppp;
 
Materialized view log dropped
 
SQL>  create materialized view log on pppp with rowid  including new values ;
 
Materialized view log created
 
SQL>
SQL> create materialized view pppp_mview
  2  refresh fast with rowid on commit
  3  enable query rewrite
  4  as
  5  select count(*) from pppp
  6  ;
 
Materialized view created
 
SQL>
 

ORA-30353: expression not supported for query rewrite

• The defining query of the materialized view cannot contain any non-repeatable
  expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
• The query cannot contain any references to RAW or LONG RAW datatypes or object
  REFs.
• If the materialized view was registered as PREBUILT, the precision of the
  columns must agree with the precision of the corresponding SELECT expressions
  unless overridden by the WITH REDUCED PRECISION clause.

 

ORA-32401: materialized view log on "PPZHU"."PPPP" does not have new values

Materialized view log does not have new values option. Executed query and the Materialized view logs that fail are the ones that do not have new values option:

1. SQL> select
LOG_OWNER
, MASTER
, SEQUENCE
, INCLUDE_NEW_VALUES
from dba_mview_logs
where LOG_OWNER in ('PJI','APPS','HRI','FII','POA')
and substr(MASTER,1,3) in ('PJI','HRI','FII','POA')
order by 1,2;

APPS POA_IDL_BS_J_MV NO NO
APPS POA_MID_BS_J_MV NO NO
APPS POA_POD_BS_J_MV NO NO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-715168/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-715168/