ORACLE＿远程数据库同步

在本地建立两张表(T1,T2)，这两张表和远程的表结构一样，通过触发器实现数据的同步，然后对本地的两张表进行物化，再在物化视图上建立触发器，实时的修改T表(并没有考虑大字段的情况)。
该方案是测试成功的，源数据库是oracle10.2.0.3.0,目标数据库是oracle10.2.0.3.0,并且做到了数据的实时更新。
1、在源数据库创建database link,确保两台服务器可以连通。
首先在oracle 的E:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora配置文件里添加远程oracle服务器的实例，具体如下：

SQL code

   
   

    
    
ORCL103 
    
    =
    
    
  (DESCRIPTION 
    
    =
    
    
    (ADDRESS 
    
    =
    
     (PROTOCOL 
    
    =
    
     TCP)(HOST 
    
    =
    
     
    
    192.168
    
    .
    
    1.103
    
    )(PORT 
    
    =
    
     
    
    1521
    
    ))
    (CONNECT_DATA 
    
    =
    
    
      (SERVER 
    
    =
    
     DEDICATED)
      (SERVICE_NAME 
    
    =
    
     orcl)
    )
  )


   
   

然后执行创建database link的语句：

SQL code

   
   

    
    

    
    create
    
     
    
    database
    
     link ORCL103.REGRESS.RDBMS.DEV.US.ORACLE.COM
  connect 
    
    to
    
     CCPPH1
  using 
    
    '
    
    orcl103
    
    '
    
    ;


   
   

然后测试是否已连通，在命令窗口输入：
Select count(*) from T1@orcl103;
2、在本地建表(T1, T2),要和源数据库中的表结构一致。

SQL code

   
   

    
    

    
    --
    
    T1表
    
    

    
    Create
    
     
    
    table
    
     T1 
    
    as
    
     
    
    select
    
     
    
    *
    
     
    
    from
    
     T1
    
    @orcl103
    
    ;

    
    alter
    
     
    
    table
    
     T1 
    
    add
    
     
    
    constraint
    
     PK_ID 
    
    primary
    
     
    
    key
    
     (ID);


    
    --
    
    T2表
    
    

    
    Create
    
     
    
    table
    
     T2 
    
    as
    
     
    
    select
    
     
    
    *
    
     
    
    from
    
     T2
    
    @orcl103
    
    ;

    
    alter
    
     
    
    table
    
     T2 
    
    add
    
     
    
    constraint
    
     PK_ID 
    
    primary
    
     
    
    key
    
     (ID);


   
   

3、在源数据库上分别建立触发器

SQL code

   
   

    
    

    
    --
    
    T1表触发器
    
    

    
    create
    
     
    
    or
    
     
    
    replace
    
     
    
    trigger
    
     TRI_T1
after 
    
    insert
    
     
    
    or
    
     
    
    update
    
     
    
    or
    
     
    
    delete
    
     
    
    on
    
     T1

    
    for
    
     each row

    
    begin
    
    

    
    if
    
     deleting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    删除
    
    '
    
    );
      
    
    delete
    
     
    
    from
    
     T1
    
    @orcl101
    
     
    
    where
    
     id
    
    =
    
    :old.id;

    
    end
    
     
    
    if
    
    ;

    
    if
    
     inserting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    插入
    
    '
    
    );
      
    
    insert
    
     
    
    into
    
     T1
    
    @orcl101
    
    (id,name)
      
    
    values
    
    (:new.id,:new.name);

    
    end
    
     
    
    if
    
    ;

    
    if
    
     updating 
    
    then
    
    
     dbms_output.put_line(
    
    '
    
    修改
    
    '
    
    );
     
    
    update
    
     T1
    
    @orcl101
    
     
    
    set
    
     id
    
    =
    
    :new.id,name
    
    =
    
    :new.name 
    
    where
    
     id
    
    =
    
    :old.id;

    
    end
    
     
    
    if
    
    ;

    
    end
    
     TRI_T1;

    
    --
    
    T2表触发器
    
    

    
    create
    
     
    
    or
    
     
    
    replace
    
     
    
    trigger
    
     TRI_T2
after 
    
    insert
    
     
    
    or
    
     
    
    update
    
     
    
    or
    
     
    
    delete
    
     
    
    on
    
     T2

    
    for
    
     each row

    
    begin
    
    

    
    if
    
     deleting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    删除
    
    '
    
    );
      
    
    delete
    
     
    
    from
    
     T2
    
    @orcl101
    
     
    
    where
    
     id
    
    =
    
    :old.id;

    
    end
    
     
    
    if
    
    ;

    
    if
    
     inserting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    插入
    
    '
    
    );
      
    
    insert
    
     
    
    into
    
     T2
    
    @orcl101
    
    (id,name)
      
    
    values
    
    (:new.id,:new.name);

    
    end
    
     
    
    if
    
    ;

    
    if
    
     updating 
    
    then
    
    
     dbms_output.put_line(
    
    '
    
    修改
    
    '
    
    );
     
    
    update
    
     T2
    
    @orcl101
    
     
    
    set
    
     id
    
    =
    
    :new.id,name
    
    =
    
    :new.name 
    
    where
    
     id
    
    =
    
    :old.id;

    
    end
    
     
    
    if
    
    ;

    
    end
    
     TRI_T2;


   
   

4、在目标数据库创建物化视图日志

SQL code

   
   

    
    

    
    --
    
    创建物化视图日志
    
    

    
    create
    
     materialized 
    
    view
    
     
    
    log
    
     
    
    on
    
     T1 
    
    with
    
     rowid;

    
    create
    
     materialized 
    
    view
    
     
    
    log
    
     
    
    on
    
     T2 
    
    with
    
     rowid;


   
   

5、在目标数据库创建物化视图

SQL code

   
   

    
    

    
    create
    
     materialized 
    
    view
    
     MV_T
refresh fast 
    
    on
    
     
    
    commit
    
    

    
    as
    
    

    
    select
    
     t1.rowid 
    
    as
    
     t1rowid,t2.rowid 
    
    as
    
     t2rowid,t1.id,t1.name,t2.id 
    
    as
    
     t2id,t2.name 
    
    as
    
     t2name 
    
    from
    
     T1 t1,T2 t2 
    
    where
    
     t1.id 
    
    =
    
     t2.id;


   
   

6、在目标数据库创建基于物化视图的触发器

SQL code

   
   

    
    

    
    create
    
     
    
    or
    
     
    
    replace
    
     
    
    trigger
    
     TRI_T
after 
    
    insert
    
     
    
    or
    
     
    
    update
    
     
    
    or
    
     
    
    delete
    
     
    
    on
    
     mv_T

    
    for
    
     each row

    
    begin
    
    

    
    if
    
     deleting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    删除
    
    '
    
    );
      
    
    delete
    
     
    
    from
    
     T 
    
    where
    
     ID
    
    =
    
    :old.ID;

    
    end
    
     
    
    if
    
    ;

    
    if
    
     inserting 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    插入
    
    '
    
    );
      
    
    insert
    
     
    
    into
    
     T(id,name)
      
    
    values
    
    (:new.id,:new.name);

    
    end
    
     
    
    if
    
    ;

    
    if
    
     updating 
    
    then
    
    
      dbms_output.put_line(
    
    '
    
    修改
    
    '
    
    );
     
    
    update
    
     T 
    
    set
    
     id
    
    =
    
    :new.id,name
    
    =
    
    :new.name 
    
    where
    
     id
    
    =
    
    :old.id;

    
    end
    
     
    
    if
    
    ;

    
    end
    
     TRI_T;


   
   

已完毕，如果数据库的版本不同，有时在执行更新的时候会报内部错误，但是数据确实是已经更新过来了。