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