时间戳

首页 > 解决方案 > 物化视图没有刷新

oracle - 物化视图没有刷新

问题描述

在 MV 下创建，但即使在定义刷新间隔后也没有更新，同时在基表上创建 MV

 Create Materialized view sim_job
    
       refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as
    SELECT TIMESTAMP,
    
     SUM(fs_count) AS fs_count
    
    
FROM
  (SELECT TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY') AS TIMESTAMP,
    COUNT(UNIQUE A.JID)                         AS FS_COUNT
  FROM LAS_ACT_PROD A,
    LAS_LOGIN B
  WHERE A.ID             IN ('LAS020')
  AND B.ROLE             IN ('TS_SW_TCH','TS_SE_TCH','TS_W_TCH','TS_W_TCH')
  AND TRUNC(A.TIMESTAMP)       = TRUNC(SYSDATE-1)
  AND A.ID                 =B.ID
  AND TRUNC(B.LOGINTIME) = TRUNC(SYSDATE-1)
  GROUP BY B.ROLE,
    TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY')
  )
GROUP BY TIMESTAMP

标签： oracleplsqloracle11g

解决方案

您的语法似乎值得怀疑：TIMESTAMP是数据类型而不是列值。

子句中的表FROM丢失。

无论如何，这有效：

create table tab as 
select rownum fs_count
from dual 
connect by level <= 10;

Create Materialized view sim_job
   refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as
SELECT CURRENT_TIMESTAMP TS,
 SUM(fs_count) AS fs_count
FROM tab;

最后刷新日期可以用

select LAST_REFRESH_DATE from user_mviews where MVIEW_NAME = 'SIM_JOB';

LAST_REFRESH_DATE  
-------------------
10.08.2021 20:07:50

如果您在一分钟后重复它，您应该会看到新的刷新

LAST_REFRESH_DATE  
-------------------
10.08.2021 20:08:50

如果没有，请检查是否依赖于您的 Oracle 版本，USER_JOBS或者USER_SCHEDULER_JOBS刷新 MV 的作业是否正在运行（即没有损坏）。

对于 Oracle 11，您将获得jobID

select owner, name, rname, REFGROUP, job, interval, to_char(next_date,'MM/DD/YYYY HH24:MI:SS') next_date 
from DBA_REFRESH_CHILDREN ;

比

 select * from USER_JOBS where  JOB = '<job>';

另请注意，您必须将 设置为job_queue_processes非零值才能运行作业。

 select value from v$parameter where name = 'job_queue_processes';

推荐阅读