在两个数据库之间进行数据同步-优快云博客

本文介绍五种不同的方法来实现Oracle数据库中的数据同步：Merge语句、全局临时表、集合处理、内联视图更新及Minus操作。每种方法都有其适用场景和优缺点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

OLAP的第一步就是从业务系统中抽取数据到数据仓库系统。
除了ETL工具Kettle，也可以使用PL/SQL

创建Source表，模拟业务系统的数据表。
create table source as select sys_guid() id ,o.* from dba_objects o where rownum<1000;
alter table source add constraint PK_source primary key (id);

创建Target表，模拟数据仓库中的表。
create table target as select * from source where 1=0;
alter table target add constraint PK_target primary key (id);

创建DB LINK，remote模拟业务系统的数据库
create database link remote connect to username identified by xxxxxx using 'remote';

因为业务系统的数据是变化的，相较于数据仓库的表，他可能更新了一些数据，也可能修改了一些数据。
比对业务表和数据仓库表的数据，
如果ID相同，并且数据有变化
则根据ID更新数据仓库的表(target)
如果业务系统的数据ID(source)，还没有出现在数据仓库的表中(target)
则在数据仓库的表中新增这个记录。

本质都是Oracle Merge的功能，只不过尝试另外几种方法。

1.merge

mergeintotarget tusing(select*fromsource@remote)s

on(t.id=s.id)

when matched

then

updateset t.owner=s.owner,

t.object_name=s.object_name,

t.subobject_name=s.subobject_name,

t.object_id=s.object_id,

t.data_object_id=s.data_object_id,

t.object_type=s.object_type,

t.created=s.created,

t.last_ddl_time=s.last_ddl_time,

t.timestamp=s.timestamp,

t.status=s.status,

t.temporary=s.temporary,

t.generated=s.generated,

t.secondary=s.secondary,

t.namespace=s.namespace,

t.edition_name=s.edition_name

whennotmatched

then

insertvalues

(

s.id,

s.owner,

s.object_name,

s.subobject_name,

s.object_id,

s.data_object_id,

s.object_type,

s.created,

s.last_ddl_time,

s.timestamp,

s.status,

s.temporary,

s.generated,

s.secondary,

s.namespace,

s.edition_name

);

2.全局临时表。
首先将远程业务系统的数据放入临时表，
然后根据ID更新数据，如果数据的内容没有变化，则不更新。
最后插入业务系统中新建的数据。

createglobal temporarytabletmp

oncommit preserverows

as

select*fromtargetwhere1=0;

insertintotmpselect*fromsource@remote;

updatetarget tset

(

t.owner,

t.object_name,

t.subobject_name,

t.object_id,

t.data_object_id,

t.object_type,

t.created,

t.last_ddl_time,

t.timestamp,

t.status,

t.temporary,

t.generated,

t.secondary,

t.namespace,

t.edition_name

)

=

(select

tmp.owner,

tmp.object_name,

tmp.subobject_name,

tmp.object_id,

tmp.data_object_id,

tmp.object_type,

tmp.created,

tmp.last_ddl_time,

tmp.timestamp,

tmp.status,

tmp.temporary,

tmp.generated,

tmp.secondary,

tmp.namespace,

tmp.edition_name

fromtmpwheret.id=tmp.id)

whereexists(

select*fromtmpwheretmp.id=t.idandnot(

tmp.owner=t.ownerand

tmp.object_name=t.object_nameand

tmp.subobject_name=t.subobject_nameand

tmp.object_id=t.object_idand

tmp.data_object_id=t.data_object_idand

tmp.object_type=t.object_typeand

tmp.created=t.createdand

tmp.last_ddl_time=t.last_ddl_timeand

tmp.timestamp=t.timestampand

tmp.status=t.statusand

tmp.temporary=t.temporaryand

tmp.generated=t.generatedand

tmp.secondary=t.secondaryand

tmp.namespace=t.namespaceand

tmp.edition_name=t.edition_name

)

);

insertintotarget

select*fromtmpwherenotexists(

select*fromtarget twheret.id=tmp.id);

3.集合处理
为了简单，没有进行内容变化的判断

declare

typetabistableoftarget%rowtype;

l_rowtab;

cursor curisselect*fromsource@remote;

begin

open cur;

fetch cur bulk collectintol_row;

close cur;

forall iin1..l_row.count

updatetargetsetrow=l_row(i)whereid=l_row(i).id;

insertintotargetselect*fromsource@remote
 s

wherenotexists(select*fromtarget
 twheret.id=s.id);

commit;

end;

/

4.内联视图更新
没有写更新后插入的步骤，插入的实现是相同的。

update(

select

s.id s1,

s.owner s2,

s.object_name s3,

s.subobject_name s4,

s.object_id s5,

s.data_object_id s6,

s.object_type s7,

s.created s8,

s.last_ddl_time s9,

s.timestamp s10,

s.status s11,

s.temporary s12,

s.generated s13,

s.secondary s14,

s.namespace s15,

s.edition_name s16,

t.id t1,

t.owner t2,

t.object_name t3,

t.subobject_name t4,

t.object_id t5,

t.data_object_id t6,

t.object_type t7,

t.created t8,

t.last_ddl_time t9,

t.timestamp t10,

t.status t11,

t.temporary t12,

t.generated t13,

t.secondary t14,

t.namespace t15,

t.edition_name t16

fromtarget t inner join source@remote son(s.id=t.id)

where

not

(

s.owner=t.ownerand

s.object_name=t.object_nameand

s.subobject_name=t.subobject_nameand

s.object_id=t.object_idand

s.data_object_id=t.data_object_idand

s.object_type=t.object_typeand

s.created=t.createdand

s.last_ddl_time=t.last_ddl_timeand

s.timestamp=t.timestampand

s.status=t.statusand

s.temporary=t.temporaryand

s.generated=t.generatedand

s.secondary=t.secondaryand

s.namespace=t.namespaceand

s.edition_name=t.edition_name

)

)

set

t1=s1,

t2=s2,

t3=s3,

t4=s4,

t5=s5,

t6=s6,

t7=s7,

t8=s8,

t9=s9,

t10=s10,

t11=s11,

t12=s12,

t13=s13,

t14=s14,

t15=s15,

t16=s16

;

5.Minus
先插入业务表中新增的记录，然后对比修改。

declare

typetabistableoftarget%rowtype;

l_rowtab;

cursor curisselect*fromsource@remoteminusselect*fromtarget;

begin

insertintotargetselect*fromsource@remote
 s

wherenotexists(select*fromtarget
 twheret.id=s.id);

open cur;

fetch cur bulk cllectintol_row;

close cur;

forall iin1..l_row.count

updatetargetsetrow=low(i)whereid=l_row(i).id;

commit;

end;

/