【Oracle】物化视图相关SQL

--记录最后一次刷新时间

SELECT *
FROM all_mview_refresh_times;

--记录刷新耗时

Select Mview_Name,
Last_Refresh_Date "START_TIME",
Case
When Fullrefreshtim <> 0 Then
Last_Refresh_Date + Fullrefreshtim / 60 / 60 / 24
When Increfreshtim <> 0 Then
Last_Refresh_Date + Increfreshtim / 60 / 60 / 24
Else
Last_Refresh_Date
End "END_TIME",
Refresh_Method,
Fullrefreshtim,
Increfreshtim
From All_Mview_Analysis
Where Mview_Name = 'CUX_AR_CUST_TRX_DATA_MV';--物化视图名称

--查看正在刷新的物化视图的session 情况

SELECT *
FROM v$mvrefresh;

--查看物化视图对应的job

SELECT j.job,
j.priv_user,
r.rowner,
r.rname,
j.broken
FROM dba_refresh r,
dba_jobs j
WHERE r.job = j.job
ORDER BY 1;

--drop掉正在刷新的物化视图
1. 查到job的session情况
2. 由于杀掉回话之后，job会重新的re-schedule的刷新操作，所以需要将job标记为broken

BEGIN
dbms_job.broken(&job_id, TRUE);
COMMIT;
END;

--3.杀掉回话
--drop 物化视图