1.建立一个简单的物化视图
create table student_info ( sno number(10) constraint pk_si_sno primary key, sname varchar2(10), sex varchar2(2), create_date date );
CREATE MATERIALIZED VIEW mv_student_info BUILD IMMEDIATE REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE + 3/144 AS SELECT * from student_info
创建一个按照时间收集的物化视图
SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE "START_TIME", FULLREFRESHTIM, INCREFRESHTIM , REFRESH_METHOD, SUMMARY , INC_REFRESHABLE, INVALID , REWRITE_ENABLED FROM ALL_MVIEW_ANALYSIS WHERE OWNER='LBCDBA' ORDER BY LAST_REFRESH_DATE DESC; 2 3 4 5 6 7 8 9 10 11 12 MVIEW_NAME START_TIME ------------------------------------------------------------ ------------ FULLREFRESHTIM INCREFRESHTIM REFRESH_METHOD SU IN IN RE -------------- ------------- ---------------- -- -- -- -- MV_STUDENT_INFO 07-SEP-23 0 0 FORCE N Y N N
查看物化视图的状态，发现物化视图是可用的，
执行insert 数据
insert into lbcdba.student_info (sno, sname, sex, create_date) values (10, '王五', 'n', sysdate);
在查看物化视图的状态
SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE "START_TIME", FULLREFRESHTIM, INCREFRESHTIM , REFRESH_METHOD, SUMMARY , INC_REFRESHABLE, INVALID , REWRITE_ENABLED FROM ALL_MVIEW_ANALYSIS WHERE OWNER='LBCDBA' ORDER BY LAST_REFRESH_DATE DESC; 2 3 4 5 6 7 8 9 10 11 12 MVIEW_NAME START_TIME ------------------------------------------------------------ ------------ FULLREFRESHTIM INCREFRESHTIM REFRESH_METHOD SU IN IN RE -------------- ------------- ---------------- -- -- -- -- MV_STUDENT_INFO 07-SEP-23 0 0 FORCE N Y Y N
发现物化视图变为不可用了，
SQL> / OBJECT_NAME STATUS OBJECT_TYPE -------------------- -------------- -------------------------------------- MV_STUDENT_INFO VALID TABLE MV_STUDENT_INFO INVALID MATERIALIZED VIEW
并且查看dba_objects 也是不可用状态
ORACLE 认为只有当物化视图正在运行时候才是可用的，如果设置了一天或者比较长时间的时间间隔运行物化视图会变成不可用，这是预期行为，那么如何判断物化视图是否可用呢？