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博客

ORA-30353,ORA-32401

最新文章