DBLink+Trigger Oracle表同步

来源：互联网发布：网络流行语论文编辑：程序博客网时间：2024/05/29 18:47

1、源数据库和新数据库各建表

create table test_user(id number(10) primary key, name varchar2(10), age number(3));

插入一条测试数据

insert into test_user(id, name, age) values(3,'Tod',40);

2、新数据库中，建立DBLink

create public database link dblink_test_user connect to 用户名 identified by 密码
using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)';

3、新数据库中，测试DBLink

select * from test_user@dblink_test_user;

4、源数据库中为表创建快照日志

create snapshot log on test_user;

5、新数据库中创建快照

create snapshot sn_test_user as select * from test_user@dblink_test_user;

6、快照同步时间

alter snapshot sn_test_user refresh fast start with sysdate+30/(24*60*60) next sysdate+30/(24*60*60); --30秒之后执行,每隔30秒同步一次

7、创建触发器

create or replace trigger trg_test_user_afr after insert or update or delete on sn_test_user for each row
begin
    if deleting then
      delete from test_user where id=:old.id;
    end if;
    if inserting then
      insert into test_user(id,name,age)
      values(:new.id,:new.name,:new.age);
    end if;
    if updating then
      update test_user set name=:new.name,age=:new.age where id=:old.id;
    end if;
    end trg_test_user_afr;

查询快照同步时间

select last_date,next_date,what from user_jobs order by next_date;