Skip to Main Content
  • Questions
  • Materialized view fast refresh ORA-12054: cannot set the ON COMMIT refresh attribute

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: June 24, 2020 - 6:17 am UTC

Last updated: June 24, 2020 - 1:08 pm UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Team,

Is it not possible to refresh a subset a data from a specific partition in Materialized view? Kindly advice.

demo@PDB1> create table t1
  2  partition by list( a_active_flag )
  3  ( partition PY values('Y'),
  4    partition PN values('N') )
  5  as
  6  select a.*, cast('Y' as varchar2(1)) as a_active_flag
  7  from all_objects a;

Table created.

demo@PDB1> create table t2
  2  partition by list( a_active_flag )
  3  ( partition PY values('Y'),
  4    partition PN values('N') )
  5  as
  6  select a.*, cast('Y' as varchar2(1)) as a_active_flag
  7  from all_objects a;

Table created.

demo@PDB1> create materialized view log on t1
  2  with rowid,sequence
  3  including new values;

Materialized view log created.

demo@PDB1>
demo@PDB1> create materialized view log on t2
  2  with rowid,sequence
  3  including new values;

Materialized view log created.

demo@PDB1> create materialized view t1_t2_mv
  2  build immediate
  3  refresh fast on commit
  4  enable query rewrite
  5  as
  6  select t1.owner, t1.object_type, t1.object_name,
  7      t2.created,
  8      t1.rowid as t1_rowid,
  9      t1.rowid as t2_rowid,
 10      t1.a_active_flag as t1_flag,
 11      t2.a_active_flag as t2_flag
 12  from t1
 13   left outer join t2
 14  on ( t1.object_id = t2.object_id
 15    and t2.a_active_flag ='Y' )
 16  where t1.a_active_flag ='Y'
 17  and t1.owner ='SYS';
where t1.a_active_flag ='Y'
                        *
ERROR at line 16:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

and Chris said...

When you get errors setting the ON COMMIT property, explain the query to see what the issue is:

create table mv_capabilities_table (
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;

begin 
  dbms_mview.explain_mview(q'[
select t1.owner, t1.object_type, t1.object_name,
    t2.created,
    t1.rowid as t1_rowid,
    t1.rowid as t2_rowid,
    t1.a_active_flag as t1_flag,
    t2.a_active_flag as t2_flag
from t1
left outer join t2
on t1.object_id = t2.object_id
and t2.a_active_flag ='Y' 
where t1.a_active_flag ='Y'
and t1.owner ='SYS']');
end;
/

select capability_name, possible, msgtxt 
from   mv_capabilities_table
where  capability_name like 'REFRESH%';

CAPABILITY_NAME                  POSSIBLE    MSGTXT                                                                
REFRESH_COMPLETE                 Y           <null>                                                                 
REFRESH_FAST                     N           <null>                                                                 
REFRESH_FAST_AFTER_INSERT        N           inline view or subquery in FROM list not supported for this type MV    
REFRESH_FAST_AFTER_INSERT        N           inline view or subquery in FROM list not supported for this type MV    
REFRESH_FAST_AFTER_INSERT        N           view or subquery in from list                                          
REFRESH_FAST_AFTER_ONETAB_DML    N           see the reason why REFRESH_FAST_AFTER_INSERT is disabled               
REFRESH_FAST_AFTER_ANY_DML       N           see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled           
REFRESH_FAST_PCT                 N           PCT FAST REFRESH is not possible if query contains an inline view  


So, the error is nothing to do with partitioning!

But... the query doesn't contain a subquery either.

Sadly the issue is down to join syntax. You need to use Oracle-style (outer) joins to get fast refresh.

Change the query to these joins and the error changes:

truncate table mv_capabilities_table;
begin 
  dbms_mview.explain_mview(q'[
select t1.owner, t1.object_type, t1.object_name,
    t2.created,
    t1.rowid as t1_rowid,
    t1.rowid as t2_rowid,
    t1.a_active_flag as t1_flag,
    t2.a_active_flag as t2_flag
from t1, t2
where t1.a_active_flag ='Y'
and   t1.owner ='SYS'
and   t1.object_id = t2.object_id (+)
and   t2.a_active_flag (+) ='Y' ]');
end;
/

select capability_name, possible, msgtxt 
from   mv_capabilities_table
where  capability_name like 'REFRESH%';

CAPABILITY_NAME                  POSSIBLE    MSGTXT                                                                                          
REFRESH_COMPLETE                 Y           <null>                                                                                           
REFRESH_FAST                     N           <null>                                                                                           
REFRESH_FAST_AFTER_INSERT        N           the SELECT list does not have the rowids of all the detail tables                                
REFRESH_FAST_AFTER_ONETAB_DML    N           see the reason why REFRESH_FAST_AFTER_INSERT is disabled                                         
REFRESH_FAST_AFTER_ANY_DML       N           see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                                     
REFRESH_FAST_PCT                 N           PCT FAST REFRESH is not possible if query contains LEFT OUTER, RIGHT OUTER or FULL OUTER JOIN 


Look closely at your query and you'll see there's another issue with it.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks.

Rajeshwaran, Jeyabal, June 24, 2020 - 3:27 pm UTC

Thanks, that helps.
completely forgot that "dbms_mview.explain_mview" method !

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.