1. 查看物化视图相关信息:
select * from dba_mview_logs ;
SELECT * FROM dba_MVIEWS;
SELECT * FROM dba_MVIEW_REFRESH_TIMES;
select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where lower(segment_name)='fwcatns_boatmap';
select * from dba_mview_logs where lower(master)='fwcatns_pdd_rdstrip' ;
物化视图不会自动创建原表索引,我们这里先记录其索引信息
create materialized view log on SAPSR3.ZTMM0117 with rowid;
create materialized view SAPSR3.ZTMM0057 tablespace sapsr3 refresh fast on demand with rowid start with sysdate next SYSDATE + 10/1440 enable query rewrite as select * from SAPSR3.ZTMM0057@SAPPROD;
这里创建在 2.2 中获取的索引,如果需要的话
DROP MATERIALIZED VIEW LOG ON SAPSR3.GG_ZLX_ZHU;
DROP MATERIALIZED VIEW SAPSR3.GG_ZLX_ZHU;
EXEC DBMS_MVIEW.Refresh('SPCR.DDM_CUST','C'); EXEC DBMS_MVIEW.Refresh('SPCR.DDM_CUST','F'); exec dbms_ijob.run(4)
ALTER MATERIALIZED VIEW mview_name COMPILE;
alter materialized view sapsr3.inob refresh start with sysdate next sysdate+10/1440