【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 m.*,
       p.SPID,
       'alter system kill session ' || '''' || to_char(s.sid) || ',' ||
       to_char(s.serial#) || ',@' || s.inst_id || '''' || ' immediate ;' kill_statement,
       'kill -9 ' || p.spid server_kill_statement
  FROM gv$mvrefresh m, gv$session s, gv$process p
 where m.SID = s.SID
   and m.SERIAL# = s.SERIAL#
   and m.INST_ID = s.INST_ID
   and s.paddr = p.addr
   and s.INST_ID = p.INST_ID;

--查看物化视图对应的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 物化视图