Oracle跨库数据同步以及跨库将一张表数据导入到另一张表

首先我们先明确需求是什么

一、第一我们假设A表是主表，B表是副表，业务需求是是将A表的数据导入到B表，好那么我们需要做的就是采用dblink的方式，将A表所有数据查询出来然后插入到B表。ps：这里我将给出一张测试用表USR（A，B两张表都是USR）

操作如下：

1、首先我们登录A表数据库，然后以system身份登录

2、赋予源库的用户xxx(这里就是)创建dblink的权限我这里是lyq（这个是你数据库用户，看你自己的就好）

语句如下：grant CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK to LYQ;  

然后我们查询下赋予权限是否成功，语句如下：

select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

3、用LYQ身份登录数据库，然后创建dblink

create database link DBLINK_TEST connect to scott identified  by "123456" using '172.16.159.26:1521/test';

好了到这一步我们需要进行详细的说明，首先看语句中的SCOTT，这个是B表所属数据库的用户名； identified  by "123456"这里的123456是B表数据库的密码；172.16.159.26:1521/test，这里的172.16.159.26：1521，是B表所在的数据的地址，1521为Oracle端口号，test是数据库名。

在创建dblink结束后我们将其重命名一下（同义词）：

create or replace SYNONYM USER_COPY for "USR" @DBLINK_TEST;

然后我们测试下创建dblink成功没有，执行下边的语句

select * from USER_COPY;

在平时创建时我们可能会创建失败这个时候就需要删除dblikn还有同义词，我将语句放在下边

drop database link DBLINK_TEST;
drop synonym USER_COPY;

4、将A表数据导入到B表

这里我们是跨库操作所以创建了dblink，有了dblink我们执行下边这句sql就好了

大致格式：insert into USER_COPY select * from tableA (这里可以输入过滤条件，比如where id=xxx)

注意这里的 USER_COPY就是我们的B表，没看明白的返回去看看第3步，执行晚之后我们看看B表的数据

到这里就完事了

二、数据同步

数据同步，我这里的遇到的业务需求是将A表所有数据导入到B表，然后当A表中有DML（增删改）操作时B表也要随之变化，在这里有第一步做铺垫我们后续工作就方便了，思路就是dblink配合TRIGGER（触发器），一旦检测到DML操作时我们就更新B表

1、用system身份登录A表所属数据库对角色（LYQ）进行赋权

GRANT CREATE ANY PROCEDURE TO "LYQ" ;
GRANT CREATE ANY SEQUENCE TO "LYQ" ;
GRANT CREATE ANY TRIGGER TO "LYQ" ;
GRANT CREATE ANY TABLE TO "LYQ" ;
GRANT UNLIMITED TABLESPACE TO "LYQ" ;
GRANT CREATE DATABASE LINK TO "LYQ" ;

如果需要移除权限用下边的这条语句

REVOKE SELECT ANY PROCEDURE FROM "LYQ";

2、切换回LYQ数据库角色，创建dblink

这里我们要创建dblink，步骤就是“一中的第3步”

3、创建触发器（重点）

代码如下：

CREATE OR REPLACE TRIGGER ds_trigger 
BEFORE INSERT OR UPDATE OR DELETE ON ANLIKU.USR FOR EACH ROW
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
BEGIN
    IF inserting THEN
      insert into USER_COPY(ID, NAME, AGE) values(:NEW.ID, :NEW.NAME, :NEW.AGE);
ELSIF updating THEN
UPDATE USER_COPY SET ID=:NEW.ID, NAME=:NEW.NAME, AGE=:NEW.AGE WHERE ID=:OLD.ID;
ELSIF deleting THEN
DELETE FROM USER_COPY WHERE ID=:OLD.ID;
    END IF;
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
END;

如果创建失败了就需要删除触发器重新创建，删除语句如下
DROP TRIGGER ds_trigger;

 

总结：好了到这里我们关于数据库同步的问题就算是解决了，思路就是触发器还有dblink，当然了也可以采用定时器的然后到一定的时间执行数据库的备份，有需要的朋友可以自行百度，谷歌找一找相关的博客，我这里提供了两个思路，在我本地完全能够运行，如果报错没有网络监听就需要配置Oracle网络。