Oracle普通视图和物化视图的区别及说明

/*默认情况下，如果没指定刷新方法和刷新模式，则oracle默认为force和demand。*/
create materialized view mv_tb as select * from tb_name;

/*指定物化视图每天刷新一次*/
create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1; 
/*要指定刷新时间比如每天晚上22：00定时刷新一次*/
create materialized view mv_name 
refresh force on demand 
start with sysdate 
next to_date( concat(to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');

/*创建on commit物化视图*/
create materialized view mv_tb refresh force on commit as select * from tb_name

/*创建db_link*/
create public database link dblink_his connect to system using '192.168.1.73:1521/oracle';
/*删除db_link*/
drop database link dblink_his; 
/*删除的时候报错：ora-02018：database link of same name has an open connection*/
/*关闭dblink：*/
alter session close database link dblink_his;

create materialized view log on test_mz_fee with primary key  including new values;  

create materialized view mv_test_mz_fee /*创建物化视图*/  
 build immediate  /*在视图编写好后创建*/  
  refresh fast with primary key  /*根据主表主键增量刷新（fast，增量）  */
 on demand  /*在用户需要时，由用户刷新 */ 
  enable query rewrite  /*可读写*/  
 as  
 select * from test_mz_fee@dblink_his; /*查询语句*/

create or replace procdure p_mview_refresh as  
begin  
dbms_mview.refresh('mv_test_mz_fee','f');  
end p_mview_refresh;  
 
/*方式2*/
create or replace procdure p_mview_refresh as  
begin  
dbms_mview.refresh('mv_test_mz_fee1,mv_test_mz_fee2','ff');
end p_mview_refresh;  

drop materialized view log on test_mz_fee@dblink_his;  
drop materialized view mv_test_mz_fee1;