oracle dblink+trigger触发器实现两个库数据同步_oracle 双库同步

库A数据迁移到新库B。

dblink和trigger都在有数据的库中创建：

create database link "dblink名称"
connect to "目标库登录名" identified by "目标库密码" 
using '(DESCRIPTION = 
(ADDRESS_LIST =  
(ADDRESS = 
(PROTOCOL = TCP)
(HOST = "库ip")
(PORT = "库端口")) ) 
(CONNECT_DATA =  (SERVICE_NAME = "服务名") )  )';
 
--删除dblink：drop database link 名称; 需要在创建的库执行

创建当前库的表与之相对应的映射表实现映射方式①

--drop trigger "删除触发器名称"  --删除触发器
 
CREATE
    TRIGGER "触发器名称" 
    BEFORE INSERT OR UPDATE OR DELETE 
    ON "当前库要同步的表名"
 FOR EACH ROW
   declare
 pragma autonomous_transaction; --指定自由事务处理 解决触发器中不能提交事务问题 
 BEGIN
   case
 when inserting then
   insert into "目标库需要同步的表"@"dblink名称"
   (
    id,
    accountno,
    officeid
       ) 
   values
   (
    :new.id,
    :new.accountno,
    :new.officeid
    );
  when updating then
    update "目标库需要同步的表"@"dblink名称"
    set 
      accountno    =:new.accountno,
      officeid     =:new.officeid
where id=:new.id;
    when deleting then
    delete "目标库需要同步的表"@"dblink名称" 
    where id =:old.id;
   end case;
   commit;
END;

创建当前库的表与之相对应的映射表实现映射方式②

CREATE OR REPLACE TRIGGER "触发器名称"
  AFTER INSERT OR UPDATE OR DELETE ON "当前库要同步的表名"
  FOR EACH ROW
declare
  pragma autonomous_transaction; 
BEGIN
  IF INSERTING THEN
    insert into "目标库需要同步的表"@"dblink名称"
      (
        id,
        officeid,
        currencyid
       )
    values
      (
        :new.id,
        :new.officeid,
        :new.currencyid
       );
  ELSIF UPDATING THEN
    update "目标库需要同步的表"@"dblink名称"
       set 
         officeid            = :new.officeid,
         currencyid          = :new.currencyid
     where id = :new.ID;
  ELSIF DELETING THEN
    delete from "目标库需要同步的表"@"dblink名称" where id = :old.id;
  END IF;
  commit;
END;