Create a materialized view on commit with PIVOT function
To use on commit refreshes, an MV must also be fast refreshable. 

If you're struggling to figure out why you can't set certain refresh properties for an MV, it's best to:


Create it refresh complete on demand
Run dbms_mview.explain_mview on it to see what the issues are


Create the mv_capabilities_table before doing this to store the output from the explain procedure. 

Do this and you'll find:

create table t (
  c1 int 
);

create materialized view log on t 
  with rowid, sequence ( c1 ) including new values;

insert into t values ( 1 );
insert into t values ( 2 );
commit;

create materialized view mv 
refresh complete on demand
as
  select one, two 
  from   t
  pivot (
    count(*) for c1 in ( 1 one, 2 two ) 
  );

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

exec dbms_mview.explain_mview('mv');

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

CAPABILITY_NAME                 POSSIBLE   MSGTXT                                                                
REFRESH_COMPLETE                Y          &lt;null&gt;                                                                
REFRESH_FAST                    N          &lt;null&gt;                                                                
REFRESH_FAST_AFTER_INSERT       N          SQL PIVOT or UNPIVOT is present                                       
REFRESH_FAST_AFTER_INSERT       N          subquery or named view in FROM list even after view merging           
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


As the REFRESH_FAST_AFTER_INSERT line says, SQL PIVOT or UNPIVOT is present. So you can't use on commit.

You can get around this by using old-style manual pivots:

drop materialized view mv ;
create materialized view mv 
refresh complete on demand
as
  select count ( case when c1 = 1 then 1 end ) one, 
         count ( case when c1 = 1 then 1 end ) two 
  from   t;

truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('mv');

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

CAPABILITY_NAME                 POSSIBLE   MSGTXT                                                                     
REFRESH_COMPLETE                Y          &lt;null&gt;                                                                     
REFRESH_FAST                    Y          &lt;null&gt;                                                                     
REFRESH_FAST_AFTER_INSERT       Y          &lt;null&gt;                                                                     
REFRESH_FAST_AFTER_ONETAB_DML   N          CASE expressions present in materialized view                              
REFRESH_FAST_AFTER_ANY_DML      N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled               
REFRESH_FAST_PCT                N          PCT is not possible on any of the detail tables in the materialized view   


Some fast refreshes are now possible. So you can do:

alter materialized view mv 
  refresh fast on commit;

select refresh_method, refresh_mode
from   user_mviews
where  mview_name = 'MV';

REFRESH_METHOD   REFRESH_MODE   
FAST             COMMIT