DBLINK实现两个数据库间表数据同步---张金总结版

系统教程1050 更新时间：2026-04-04 02:39:31

2024年3月15日发(作者：)

1，

在目标机上建立Oracle DB Link：

--在本地（目标机器）如下文件加入代码，连接到目标数据库上面

1.1，在network/admin/文件中加入源库的连接信息，

如：

AAA_10.5.1.3=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.5.1.3)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = AAA)

)

说明： 10.5.1.3：源库ip

AAA_10.5.1.3：创建的实例名（服务名）

1,2，在

目标机器上用sqlplus user/pwd登录：目标机器（本地）的用户名和密码，不是

源库机器的用户名和密码。

1.3，在本地机器（目标库）上用如下命令建立DB Link:

create public database link AAA_LINK connect to user identified by pwd using 'AA

A_10.5.1.3';

命令说明：

AAA_LINK：dblink的名称

user：源库的用户名

pwd ：源库的密码

AAA_10.5.1.3：配置的实例名（服务名）

说明：CREATE PUBLIC DATABASE LINK

数据库链接名

CONNECT TO

用户名

IDENTIFIED

BY

密码

USING ‘

本地配置的数据的实例名

’;

如果建立成功，会提示：

Database link created.

1.4--测试使用链接的数据库（表名@dblink名称）

select * from FA_CARD@ AAA_LINK;

1.5创建一个测试表：在目标机器和原机器上都创建

create table dblink_test

(

user_id NUMBER,

user_name VARCHAR2(100)

);

--

在其中增加一条记录：

insert into dblink_test (user_id, user_name)

values (1, '钟德荣');

4，使用如下脚本，即可同步数据表：

2.1 将源库的数据插入到目标库

方法一、

truncate table dblink_test;--

删除目标库的相应表数据

--

插入目标机器数据，来源于源库的数据

insert into dblink_test

select * from _test@KNG_LINK; --#

这里是指向要同步的来源表

,

表

名必须是

<

表所有者（源库的用户）

>.<

表名（源库的表）

>@

commit;

方法二、

merge into dblink_test b using dblink_test@KNG_LINK c on

(_ID=_ID)

--

如果

pk

（主键）值是相同则将目标库

c

将对应表的数据更新到源库

b

的对应表中

when matched then update set _NAME=_NAME

--

如果

pk

（主键）值不一至，则将目标表中的数据整条插入到源表中

when not matched then

insert values (_ID,_NAME);

--

记得

merge

后必须

commit,

否则更改未能提交

commit;

可以将以上语句作为sql脚本，然后写一个类似2.2的bat命令来作为任务执行

2.2 将目标库的数据插入或者更新到源库

--

从目标库

c

将对应表的数据插入到源库

b

的对应表中

方法一

insert into _test@KNG_LINK

select * from dblink_test;

方法二

merge into dblink_test@KNG_LINK b using dblink_test c on

(_ID=_ID)

--

如果

pk

（主键）值是相同则将目标库

c

将对应表的数据更新到源库

b

的对应表中

when matched then update set _NAME=_NAME

--

如果

pk

（主键）值不一至，则将目标表中的数据整条插入到源表中

when not matched then

insert values (_ID,_NAME);

--

记得

merge

后必须

commit,

否则更改未能提交

commit;

将以上脚本写入

中，然后建立文件，双击运行文件，那么系

统会自动将

目标库

c

将对应表的数据插入到源库

b

的对应表中，

可以将

作为任务定期执行（也可以按照3创建存储过程和任务来做）

如下图所示

5，实现数据同步

方式一、通过2所描述的方式实现数据库数据同步（已测试）

方法二、创建存储过程和任务（待测试）

--创建一个存储过程

create procedure proc_sjtb

as

begin

delete from swdx_gzryxx where is_tb='1';

insert into swdx_gzryxx(gzrybh,mc,jgdm,phone,is_tb)select

distinct(_dm),_mc,_swjg_dm,_zgy,'1' from

dxpt_sjtb@mydblink t;

delete from gdzc_jg where is_tb='1';

insert into gdzc_jg(jg_dm,jg_mc,is_tb)select

distinct(_swjg_dm),_swjg_mc,'1' from dxpt_sjtb@mydblink t;

delete from swdx_nsrxx where is_tb='1';

insert into swdx_nsrxx(nsrsbh,nsrmc,jgdm,zgydm,is_tb)select

,,_swjg_dm,_dm,'1' from dxpt_sjtb@mydblink

t;

insert into swdx_nsrphone(nsrsbh,lbbh,tel) select

,'001',_NSR_CW from dxpt_sjtb@mydblink t;

insert into swdx_nsrphone(nsrsbh,lbbh,tel) select

,'002',_NSR_FR from dxpt_sjtb@mydblink t;

insert into swdx_nsrphone(nsrsbh,lbbh,tel) select

,'003',_NSR_QT from dxpt_sjtb@mydblink t;

delete from xt_czy where is_tb='1';

insert into xt_czy(czy_id, czy_mc, jg_code, czy_lxdh,

is_zgy,is_tb,is_jgczy,CZY_YXBZ) select distinct (_dm),

_mc, _swjg_dm, _zgy,'1','1','0','Y' from

dxpt_sjtb@mydblink t;

insert into xt_czy(czy_id, czy_mc, jg_code,

is_zgy,is_tb,is_jgczy,CZY_YXBZ) select

distinct(_swjg_dm),_swjg_mc,_swjg_dm,'0','1','1','Y'

from dxpt_sjtb@mydblink t;

end;

--创建任务(每分钟执行一次)

DECLARE X NUMBER;

BEGIN

_

(job => X

,what => 'proc_sjtb;'

,next_date => to_date('09-12-2008 00:00:00','dd/mm/yyyy

hh24:mi:ss')

,interval => 'TRUNC(sysdate) + 1 +2 / (24)'

);

END;

--执行任务

--RUN(参数)是我们建立任务的时候自动生成的，指定任务号方可启动任务和删除任务

begin

_(25);

end;

--删除任务

begin

_(23);

end;

方法三、创建触发器实现实时同步（测试通过）

--KNG_LINK

数据链名称

--cztbd

源数据库的用户名

--cztbd

源数据库的密码

--KNG ORACLE SID

实例名

(

源库

)

--1

、在源数据库上，创建要同步表的快照日志（不是很确定是否是在源库建立这个快照日志）

--DBLINK_TEST

：源库表，

LOG

：快照名称

,

不虚伪

log

不能修改，否则不能创建成功

Create snapshot LOG on DBLINK_TEST;

--2

、在目标数据库上创建快照

(

被同步（源）数据库服务必须启动

) DB_LINK_KZ

：快照名称

Create snapshot DB_LINK_KZ as select * from DBLINK_TEST@KNG_LINK;

--3

、设置刷新快照时间：如下写法为每隔一分钟刷新一次

Alter snapshot DB_LINK_KZ refresh fast Start with sysdate next

sysdate+1/1440;

---

删除快照的方法

drop snapshot DB_LINK_KZ;

DROP MATERIALIZED VIEW DB_LINK_KZ;

---

手动刷新快照

begin

dbms_h('DB_LINK_KZ');

end;

---

查看快照最后一次刷新时间

SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

---

查看快照下次执行时间

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

--4

、创建触发器

:

在目标库创建未通过，

创建用于同步本地表与远程表(源数据库)数据的触发器由于创建快照后，快照在目标数据库表

现为会创建一个数据表DB_LINK_KZ，此表根据上一步骤设置的快照刷新时间自动与源数据库的

表user同步，要想实现两个数据库的真正同步，只需针对DB_LINK_KZ表创建一个触发器即可

解决，即通过触发器实现往目的数据库的指定数据表中插入数据，实现本地数据与目的数据库表

的数据同步。

写法一：（双向同步，源库表变化，则目标库表变化，目标库表变化，

则源库表变化）

说明：

如果你想双向同步,请在源数据库中执行和目标库一样的操作

（即：建立dblink，建立快照，快照刷新等）,并在双方都创建以下触

发器

CREATE OR REPLACE TRIGGER TRI_KNG_LINK

AFTER DELETE OR INSERT OR UPDATE

ON DB_LINK__user

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

declare

tmp_id number(10):=-1;

flag number(3):=0;

begin

dbms__line('begin');

if inserting then

for p in(select user_id from DBLINK_TEST where

user_id=:_id)--DBLINK_TEST

：目标库表

loop

tmp_id:=_id;

end loop;

dbms__line(tmp_id||'===------------');

if (tmp_id=-1) then

insert into DBLINK_TEST(user_id,user_name)

values(:_id,:_name);

end if;

if updating then

dbms__line('updated');

for p in(select user_name from DBLINK_TEST where

user_id=:_id)

loop

if (_name!=:_name) then

update DBLINK_TEST set user_name=:_name where

user_id=:_id;

end if;

end loop;

end if;

if deleting then

dbms__line('deleted');

delete from DBLINK_TEST where user_id=:_id;

end if;

dbms__line('end');

end TRI_KNG_LINK;

--为防止双向同步触发器死循环,所以要在触发器中增加一些判断,阻止死循环.

问题描述：

1、测试发现，在目标库的DB_LINK_KZ表中数据变化了，则目标库的表DB_LINK数据也跟

着变化，但是源库的表DB_LINK数据变化，目标库的表DB_LINK数据没有着变化

2、同时再次测试发现：使用手动快照刷新之后，目标库的DB_LINK_KZ表中数据跟着源库

的表DB_LINK数据变化，目标库的表DB_LINK数据也跟着变化（这里实现是因为有触发器）

最终解决此问题：原来是快照刷新时间问题，快照没有刷新所以才造成了最终功能没有实

现，非常郁闷！！！！！！！！！

写法二：（单向同步，只在目标库进行更新等操作）

create or replace trigger TRI_test_user_AFR

after insert or update or delete on DB_LINK_KZ

for each row

begin

if deleting then

delete from DBLINK_TEST where user_id=:_id;

end if;

if inserting then

insert into DBLINK_TEST (user_id,user_name)

values(:_id,:_name);

end if;

if updating then

update DBLINK_TEST set user_name=:_name where

user_id=:_id;--记住：更新肯定是主键一致才更新

end if;

end TRI_test_user_AFR;

6，下面再补充三点：

1、创建DB_Link时，"tns_xj_to_bj"是指服务器端文件中所定义的环境

名，但在企业中，并没有几个人有权限查看这个文件中的内容。

解决办法：将‘tns_xj_to_bj’改写成客户端文件中对应的实际连接

串。如：

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = test)

)

注意：此处“(SERVER = DEDICATED)”可有可无，并没什么影响。

2、查看所有DBLink可采用以下两种方式

SQL1：select owner,object_name from dba_objects where

object_type='DATABASE LINK';

SQL2：select * from all_db_links;

但唵更偏向用SLQ2。No Reason！呵呵！

3、删除DB_Link

drop database link ;

其中database不能更改，为你创

建的DB_Link值（采用SQL2），当然需要用创建该DB_Link的用户登录才能成功删除！

7，整个创建过程总结如下

5、1 dblink总结

--

创建

dblink

：在

ufgov/ufgov

登录

plsql

，然后之下下列脚本

create database link KNG_LINK

connect to catbd identified by catbd

using 'KNG_192.168.1.103';

--

查询

dblink

：

（表名@dblink名称）：查询的是源库的表数据

select * from FA_CARD@KNG_LINK;

--

删除

dblink

drop public database link KNG_LINK;

--

返回数据库的

GLOBAL_NAME

SELECT * FROM GLOBAL_NAME;

--

查看远程数据库是否支持高级复制功能：

value=true

为支持

select * from v$option where PARAMETER='Advanced replication';

--4

、查询已经建立的远程连接名：

select owner,object_name from dba_objects where object_type='DATABASE

LINK';

--5

删除

dblink

：

DROP PUBLIC DATABASE LINK KNG_LINK。

如果创建全局dblink，必须使用systm或sys用户，在database前加public。

5.2 快照总结

1，ORACLE的快照刷新方式refresh有三种:

fast 快速刷新，用snapshot log，只更新时间段变动部分

complete 完全刷新，运行SQL语句

force 自动判断刷新，介于fast和complete之间

快照技术提供给我们三种刷新机制,分别是:

1) Complete 完全刷新机制,即对表的所有数据进行刷新,如果表的数据量十分庞大的,此法

会消耗相当的时间;

2) Fast 快速刷新,即只对数据增量进行刷新;

3) Force 强制刷新,首先判断是否能用快速刷新机制,如不行则用完全刷新机制。

2，若在数据库B上创建快速刷新，则要在数据库A上创建快照日志对象，否则

没这个必要，即第4步。

总结呈词：快照所实现的功能就是同步源库表和目标库表中的数据，而触发

器实现的是当前库的表与表之间的操作。

本文发布于:2024-03-15，感谢您对本站的认可！

本文链接:https://www.fzithome.com/xitong/1710474847a331629.html

DBLINK实现两个数据库间表数据同步---张金总结版

发布评论取消回复

最近发表

相关推荐

标签列表

DBLINK实现两个数据库间表数据同步---张金总结版

发布评论 取消回复

最近发表

相关推荐

标签列表

发布评论取消回复