创建物化视图需要的权限:
grant create materialized view to user_name;
在目标数据库上创建MATERIALIZED VIEW:
create materialized view mv_materialized_test refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as
select * from user_info; --这个物化视图在每天10:25进行刷新
修改刷新时间:
alter materialized view mv_materialized_test refresh force on demand start with sysdate
next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');
或
alter materialized view mv_materialized_test refresh force on demand start with sysdate
next trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新
建立索引:
create index IDX_MMT_IU_TEST
on mv_materialized_test(ID,UNAME)
tablespace test_space;
删除物化视图及日志:
drop materialized view log on test_table; --删除物化视图日志:
drop materialized view mv_materialized_test; --删除物化视图