目的: 把A機(jī)dept表同步到B機(jī)的mv_dept物化視圖中.

step1. 在A機(jī)創(chuàng)建物化視圖日志 SQL> create materialized view log on scott.dept;

step2. 在B機(jī)修改作業(yè)隊(duì)列最大并行進(jìn)行數(shù) SQL> alter system set job_queue_processes=10; --不能為0

step3. 創(chuàng)建從B機(jī)連接到A機(jī)的DBLink  SQL> create database link db_link_144 connect to scott identified by tiger using 'orcl144';

step4. 在B機(jī)創(chuàng)建物化視圖 SQL>create materialized view mv_dept
    refresh fast on demand
    as
   select * from dept@db_link_144;

step5. 設(shè)定定時(shí)刷新數(shù)據(jù)作業(yè)(此時(shí)作業(yè)已進(jìn)入隊(duì)列，但不一定馬上執(zhí)行.)
SQL> BEGIN
  2       DBMS_REFRESH.MAKE (
  3          name => 'dept_ref',
  4          list => 'MV_DEPT',
  5          next_date => SYSDATE,
  6          interval => 'SYSDATE + 1/1440'); --每分鐘刷新一次
  7   END;
  8  /

SQL> commit; --記得commit; 否則沒(méi)有job.

step6. 查看job作業(yè)運(yùn)行情況
SQL> select * from dba_jobs_running;
 
or
 
SQL> select * from user_jobs;
 
step7. 在A機(jī)插入數(shù)據(jù): SQL> insert into scott.dept values(90, 'SERVICE', 'CANTON');
                                           SQL> commit;

step8. 在B機(jī)驗(yàn)證物化視圖刷新情況: 
 
SQL> select * from mv_dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 SERVICE        CANTON

如果能看到上面結(jié)果，就證明從A機(jī)的dept同步到B機(jī)的mv_dept已經(jīng)成功了.