SQL 如何知道 MATERIALIZED VIEW 更新是否正在运行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1475850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to know if MATERIALIZED VIEW update is running?
提问by andrii
I'm talking about oracle. I have a few materialized views, and they're updated from time to time (it is done with a scheduled task). It is easy to know last refresh date - just query USER_MVIEW_REFRESH_TIMES. And is there any way to know if some views are being updated in the current moment?
我说的是甲骨文。我有一些物化视图，它们会不时更新（它是通过计划任务完成的）。很容易知道上次刷新日期 - 只需查询 USER_MVIEW_REFRESH_TIMES。有没有办法知道当前是否正在更新某些视图？
采纳答案by Vincent Malgrat
you could see indirectly if a materialized view is being refreshed by looking if a process has aquired locks on it : a materialized view should be modified only when refreshed (except FOR UPDATE materialized views used for two-way replication).
您可以通过查看进程是否已获得锁定来间接查看物化视图是否正在刷新：物化视图应仅在刷新时修改（用于双向复制的 FOR UPDATE 物化视图除外）。
This query should return rows only when your materialized view is being refreshed:
仅当刷新物化视图时，此查询才应返回行：
SELECT sid
FROM v$lock
WHERE type = 'TX'
AND id1 = (SELECT object_id
FROM all_objects
WHERE owner = :owner
AND object_name = :object_name)
回答by Somdeb
You could try:
你可以试试：
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH
to get the list of views refreshing right now.
立即获取刷新的视图列表。
回答by dpbradley
Materialized views that are defined to be refreshed on a schedule with "... START WITH... NEXT..." as part of their DDL will be executing under DBMS_JOB control. If the refresh of these MV's is in progress, you'll see values for THIS_DATE and THIS_SEC in the USER_JOBS (or DBA_JOBS) view for the corresponding job and/or a row in DBA_JOBS_RUNNING.
定义为按计划刷新的物化视图将“... START WITH... NEXT...”作为其 DDL 的一部分，将在 DBMS_JOB 控制下执行。如果正在刷新这些 MV，您将在相应作业的 USER_JOBS（或 DBA_JOBS）视图中看到 THIS_DATE 和 THIS_SEC 的值和/或 DBA_JOBS_RUNNING 中的一行。
If you've defined the MV to be refreshed on demand and are launching the refresh externally then Vincent's answer is one way to determine the state.
如果您已将 MV 定义为按需刷新并在外部启动刷新，则 Vincent 的答案是确定状态的一种方法。
