How to select refresh rate of all materialized views in Oracle-CodePudding

I want to select every refresh rate of my materialized views in Oracle. By refresh rate i mean SYSDATE 1/24 (what u get from the column info in the schema browser)

When I execute

select * from all_mviews;

it shows me the last refresh date but not the refresh rate.

For the record I am using Oracle 11g

CodePudding user response：

The dictionary view USER_REFRESH gives you the information about the next refresh.

Example MV (without a refresh group)

create materialized view mv_18 
BUILD IMMEDIATE
REFRESH COMPLETE START WITH SYSDATE 
  NEXT  SYSDATE   3/24
as
select * from dual;

gives this result - see column INTERVAL

select ROWNER, RNAME, NEXT_DATE, INTERVAL
from USER_REFRESH;

ROWNER                         RNAME                          NEXT_DATE           INTERVAL                                                                                                                                                                                                
------------------------------ ------------------------------ ------------------- ---------------
xxx                            MV_18                          06.10.2021 21:21:52 SYSDATE   3/24

If you use a refresh group it is better to look at the view USER_REFRESH_CHILDREN, because the RNAME is the refresh group name

select RNAME,INTERVAL,JOB  
from USER_REFRESH_CHILDREN
where name = 'MV_18A'; -- = mview_name

The column JOB (if not zero) points in USER_JOBS to the job that refreshs the MV.

Starting with 18g the view got a new column JOB_NAME which references the view user_scheduler_jobs for the job that is responsible for the refresh.

My observation is that starting with 19g only dbms_scheduler jobs are used for the refresh.

select RNAME,INTERVAL,JOB,JOB_NAME 
from USER_REFRESH_CHILDREN
where name = 'MV_18A'; -- = mview_name