Snapshots & Materialized Views

Query (This database is client of other masters)
    
--select db_link_user, db_link_host, count(*) num_snapshot from (
Select T.Owner, t.table_name, 
       (select sum(bytes)/1024/1024 from dba_segments where owner = s.owner and segment_name = s.table_name) size_MB, 
       t.tablespace_name, 
       S.MASTER_VIEW, S.MASTER_OWNER, S.MASTER,
       S.MASTER_LINK
	   , dbl.owner db_link_owner, dbl.db_link db_link, dbl.username db_link_target_user, dbl.host db_link_host,  
       decode(to_char(S.LAST_REFRESH, 'mm-dd-yyyy'), '00-00-0000', to_date(null), S.LAST_REFRESH) last_refresh,
	   dba_jobs.last_date job_last_date,
       decode(to_char(S.START_WITH, 'mm-dd-yyyy'), '00-00-0000', to_date(null), s.start_with) start_with,
	   dba_jobs.next_date job_next_date,
	   cast(S.START_WITH as timestamp) - cast(S.LAST_REFRESH as timestamp) interval_snapshot,
	   cast(dba_jobs.next_date as timestamp) - cast(dba_jobs.last_date as timestamp) interval_job,
	   r.rname refresh_group, dba_jobs.job, dba_jobs.broken job_broken, dba_jobs.what job_what, 
	   S.QUERY, 
	   R.ROLLBACK_SEG, S.CAN_USE_LOG, S.UPDATABLE, S.REFRESH_METHOD,
	   S.ERROR, S.TYPE,
	   dba_jobs.INTERVAL job_interval,
       r.INTERVAL dba_refresh_interval, s.NEXT snapshot_next,
	   S.UPDATE_TRIG,
       S.UPDATE_LOG, S.MASTER_ROLLBACK_SEG, S.STATUS, S.REFRESH_MODE, S.PREBUILT,
	   M.REWRITE_ENABLED, M.REWRITE_CAPABILITY, M.BUILD_MODE, M.FAST_REFRESHABLE,
	   M.LAST_REFRESH_TYPE, M.STALENESS, M.AFTER_FAST_REFRESH, M.COMPILE_STATE, M.USE_NO_INDEX
FROM   DBA_TABLES T, DBA_SNAPSHOTS S, SYS.DBA_MVIEWS M,
SYS.DBA_REFRESH R, dba_db_links dbl, dba_jobs
WHERE  T.OWNER      = S.OWNER
AND    T.TABLE_NAME = S.TABLE_NAME
AND    M.OWNER      = S.OWNER
AND    M.MVIEW_NAME = S.NAME
--AND    R.ROWNER (+) = S.OWNER
--AND    R.RNAME  (+) = S.NAME
and R.refgroup (+)= s.refresh_group
and    dbl.owner (+)= s.owner
and    dbl.db_link (+)= substr(S.MASTER_LINK, 2, length(S.MASTER_LINK))
and dba_jobs.job (+)= r.job
--) group by db_link_user, db_link_host
order by r.rname

Altering a MV refresh interval

You only need to alter the interval of the relative job:

begin
  dbms_job.interval(52090, 'case when sysdate > trunc(sysdate,''hh24'')+40/1440 then trunc(sysdate,''hh24'')+1/48 else trunc(sysdate,''hh24'')+1/48+15/1440 end');
end;

At next job run the big query above will display updated refresh times

Query (This database is master for clients)

select * from DBA_MVIEW_LOGS

Grant
grant on commit refresh to aldo;

Refresh on commit Snapshot
CREATE MATERIALIZED VIEW ALDO.CUBE_FATTURE
BUILD IMMEDIATE  
REFRESH FAST 
ON COMMIT  
ENABLE QUERY REWRITE 
AS
select calc_data_fattura_anno, calc_data_fattura_mese, calc_data_fattura_giorno
, sum(n_importo) n_importo, COUNT(*) AS count_all 
, GROUPING_ID(calc_data_fattura_anno, calc_data_fattura_mese, calc_data_fattura_giorno) AS gid
from lavF_fatture, lav_commesse
, aziende azDest
where lav_commesse.id(+)= lavF_fatture.id_commessa --lavF_fatture.id_commessa must be included in mvlog columns
and azDest.id(+)= lav_commesse.id_azienda
group by cube (calc_data_fattura_anno, calc_data_fattura_mese, calc_data_fattura_giorno)
               
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
     ownname => 'ALDO',
     tabname => 'LAVF_FATTURE');
END;

Materialized view log
--do not include pk 
--exclude any not cubed columns, include count() columns
--include rowid for immediate use on cubes
--necessary for each table used from mv when refresh fast
CREATE MATERIALIZED VIEW LOG ON ALDO.LAVF_FATTURE
WITH  ROWID, PRIMARY KEY
(N_IMPORTO,ID_COMMESSA,DATA_FATTURA,CALC_DATA_FATTURA_ANNO,CALC_DATA_FATTURA_MESE,CALC_DATA_FATTURA_GIORNO)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG ON ALDO.LAVF_FATTURE ADD SEQUENCE;

dbms_mview
--create necessary table
@utlxmv.sql

truncate table mv_capabilities_table

declare
  s varchar2(4000) := '... mv query ...';
begin
	 DBMS_MVIEW.EXPLAIN_MVIEW (mv =>  s);
end;

select * from mv_capabilities_table

Refresh mview
BEGIN
   --or DBMS_MVIEW
   DBMS_SNAPSHOT.REFRESH(
     list => '"ALDO"."DWH_COSTEDEVENT"',
     method => 'C',
     rollback_seg => '',
     push_deferred_rpc => FALSE,
     refresh_after_errors => false);
END;

alter materialized view "ALDO"."DWH_COSTEDEVENT" consider fresh

My questions
It is not possible altering the sql of a materialized view without refreshing it