您的当前位置：首页正文

oracle数据表数据同步公用方法

2023-11-10 来源：哗拓教育

自己写了个数据同步的方法，两个数据库之间的数据同步，自己可以通过调用存储过程，添加作业实现定时同步数据。

CREATE OR REPLACE PROCEDURE Data_sync_Common(tableName in varchar2) is v_sql VARCHAR2(20000); --????SQL pk_col_name VARCHAR2(800); --主键SQL insert_col_name_A VARCHAR2(20000); --A表字段 insert_col_name_B VARCHAR2(20000); --B表字段 update_col_name VARCHAR2(20000); --更新字段 dsql VARCHAR2(20000); local_col_in VARCHAR2(20000); TYPE cur_type IS REF CURSOR; cur_not_contain_col cur_type; CURSOR CR1 IS -- select ‘alter table ‘||table_name||‘ disable constraint ‘||constraint_name as dsql SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = ‘R‘ AND table_name = upper(tableName);BEGIN SELECT to_char(WMSYS.WM_CONCAT(‘‘‘‘ || column_name || ‘‘‘‘)) INTO local_col_in FROM user_tab_cols WHERE table_name = upper(tableName); dsql := ‘SELECT * FROM user_tab_cols@testjob_dblink1 WHERE table_name=‘‘‘ || upper(tableName) || ‘‘‘ AND column_name not IN(‘ || local_col_in || ‘)‘; dbms_output.put_line(dsql); OPEN cur_not_contain_col FOR dsql; SELECT REPLACE(to_char(WMSYS.WM_CONCAT(‘ AND a.‘ || cu.COLUMN_NAME || ‘=b.‘ || cu.COLUMN_NAME)), ‘,‘, ‘‘) INTO pk_col_name FROM user_cons_columns cu, user_constraints au WHERE cu.constraint_name = au.constraint_name AND au.constraint_type = ‘P‘ AND au.table_name = upper(tableName); IF (pk_col_name IS NULL) OR (LENGTH(pk_col_name) < 6) THEN SELECT REPLACE(to_char(WMSYS.WM_CONCAT(‘ and a.‘ || cu.COLUMN_NAME || ‘=b.‘ || cu.COLUMN_NAME)), ‘,‘, ‘‘) INTO pk_col_name FROM user_cons_columns cu, user_constraints au WHERE cu.constraint_name = au.constraint_name AND au.constraint_type = ‘U‘ AND au.table_name = upper(tableName); END IF; SELECT to_char(WMSYS.WM_CONCAT(‘b.‘ || column_name)) INTO insert_col_name_B FROM user_tab_cols WHERE table_name = upper(tableName); SELECT to_char(WMSYS.WM_CONCAT(‘a.‘ || column_name)) INTO insert_col_name_A FROM user_tab_cols WHERE table_name = upper(tableName); SELECT to_char(WMSYS.WM_CONCAT(‘a.‘ || column_name || ‘=b.‘ || column_name)) INTO update_col_name FROM user_tab_cols WHERE table_name = upper(tableName) AND column_name NOT IN (SELECT cu.COLUMN_NAME FROM user_cons_columns cu, user_constraints au WHERE cu.constraint_name = au.constraint_name AND au.constraint_type = ‘P‘ AND au.table_name = upper(tableName)); v_sql := ‘MERGE INTO ‘ || upper(tableName) || ‘@testjob_dblink1 a USING ‘ || upper(tableName) || ‘ b‘ || ‘ ON (‘ || substr(pk_col_name, 6, length(pk_col_name)) || ‘)‘; dbms_output.put_line(update_col_name); IF (update_col_name IS NOT NULL) AND (LENGTH(update_col_name) > 0) THEN v_sql := v_sql || ‘ when matched then update set ‘ || update_col_name; END IF; v_sql := v_sql || ‘ when not matched then insert (‘ || insert_col_name_A || ‘) values( ‘ || insert_col_name_B || ‘)‘; dbms_output.put_line(v_sql); EXECUTE immediate(v_sql); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm);END Data_sync_Common;-----------------------------调用上述存储过程CREATE OR REPLACE PROCEDURE DATA_sync isBEGINdata_sync_common(‘TEST_SYNC‘);COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK ;END DATA_sync;

oracle数据表数据同步公用方法

标签：

小编还为您整理了以下内容，可能对您也有帮助：

如何实现Oracle数据库之间的数据同步

为方便完成指定数据表的同步操作，可以采用dblink与merge结合的方法完成。

操作环境：此数据库服务器ip为192.168.196.76,有center与branch两个库，一般需要将center的表数据同步到branch，center为源库，branch为目标库，具体步骤如下：

1.在源库创建到目标库的dblink

create database link branch --输入所要创建dblink的名称，自定义

connect to dbuser identified by “password” --设置连接远程数据库的用户名和密码

using '192.168.196.76/branch'; --指定目标数据库的连接方式，可用tns名称

在创建dblink时，要注意，有时候可能会报用户名和密码错误，但实际上我们所输入的账户信息是正确的，此时就注意将密码的大小写按服务器上所设置的输入，并在账号密码前号加上双引号(服务器版本不同造成的)。

2.成功后验证dblink

select * from tb_bd_action@branch; --查询创建好的brach库

正常情况下，如果创建dblink成功，可采用该方式访问到远程数据库的表.

3.通过merge语句完成表数据同步

此例中需要将center库中的tb_sys_sqlscripe表同步到branch，简单的语法如下:

merge into tb_sys_sqlscripe@branch b using tb_sys_sqlscripe c on (b.pk=c.pk) --从center将表merge到branch,同步的依据是两个表的pk

when matched then update set b.sqlscripe=c.sqlscripe,b.author=c.author --如果pk值是相同则将指定表的值更新到目标表

when not matched then --如果pk值不一至，则将源表中的数据整条插入到目标表中

insert values (c.pk, c.fk, c.createtime, c.lastmodifytime,c.author,c.mole,c.deleteflag, c.scripttype);

commit; --记得merge后必须commit,否则更改未能提交

4.为方便每次需要同步时自动完成同步工作，可将该语句做成存储过程或脚本来定时执行或按要求手动执行,简单说一下创建脚本的方法:

a.创建merge文件夹

b.先将merge语句写完整后，存到merge.sql文件中

c.新建merge.bat文件，编辑后写入以下内容

sqlplus user/password@serverip/database @"%cd%\merge.sql"

两台oracle10g数据库,如何才能进行数据同步

分布实现Oracle数据库的实时数据复制的步骤是这样的：

在具体的实现之前，首先要做好设计与规划。这就需要细致分析具体的业务情况，设计出一套能够满足业务需要的方案。通常在设计过程中，需要确定出要建立的数据库站点，各站点的类型，需要复制的数据对象，以及同步方式、冲突解决方案等内容。

在设计完成之后，就可具体来实现数据复制，数据复制的实现主要包括以下几步：

（1）创建复制站点；
（2）创建组对象；
（3）配置冲突解决方案。

下面我们举一个例子来说明各步具体需要完成的工作。在这个例子中我们采用多主控站点复制方式，设有两个主控站点和两个共享数据表。两个主控站点分别为：处理站点（cl.world）和解释站点（js.wo rld）；两个数据表为测区( survey)和测线( line)。

STEP1 创建复制站点：

（1）首先以SYSTEM身份登陆主站点数据库cl.worldCONNECT system/manager@cl.world；
（2）创建用户—复制管理员，并为该用户授权复制管理员负责复制站点的创建和管理，每个复制站点都必须创建复制管理员以下为引用的内容：
　CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
　DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ’repadmin’);
END;

（3）为本站点指定传播者（传播者负责将本地最新更新的数据传播到其他站点上）：
BEGIN
　DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ’repadmin’);
END;

（4）为本站点指定接收者（接收者负责接收其他站点上的传播者传送过来的数据）：
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
　username => ’repadmin’,
　privilege_type => ’receiver’,
　list_of_gnames => NULL);
END;
（5）确定清除时间：
为了使传送过来事务队列不致过大，需要将成功加载的事务从事物队列里清除掉，这里设定每小时清除一次：
CONNECT repadmin/repadmin@cl.world
BEGIN
　DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ’SYSDATE + 1/24’,
delay_seconds => 0);
END;
在建立好站点cl.world后，以同样的方法创建站点js. world。
（6）创建各主控站点之间的调度链接：
创建各主控站点之间的调度链接需要先在各主控站点间建立数据库链接，之后为每个数据库链接定义调度时间。
首先，在处理站点上建立与解释站点的数据库链接，这里需要先建立一个公用数据库链接，供其他私有数据库链接来使用。

以下为引用的内容：

CONNECT SYSTEM/MANAGER@cl.world
CREATE PUBLIC DATABASE LINK js.world USING ’js.world’;
CONNECT repadmin/repadmin@cl.world
CREATE DATABASE LINK js.world CONNECT TO repadmin
IDENTIFIED BY repadmin;

同样，在解释站点上建立与处理站点的数据库链接。

以下为引用的内容：

CONNECT SYSTEM/MANAGER@js.world
CREATE PUBLIC DATABASE LINK cl.world USING ’cl.world’;
CONNECT repadmin/repadmin@js.world
CREATE DATABASE LINK cl.world CONNECT TO repadmin
IDENTIFIED BY repadmin;

调度链接确定本站点上的事务向其他站点发送的频度，下面的代码为10分钟一次:

以下为引用的内容：

CONNECT repadmin/repadmin@cl.world
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ’js.world’,
interval => ’SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;

在解释站点上做相同的工作STEP2 创建主控组在复制环境中，Oracle用组来管理复制对象。通过将相关的复制对象放在一个组里，从而方便对大量数据对象的管理。

这里我们假设用户模式integr ation 在处理站点和解释站点都已存在，而且表测区(survey )和测线(line)也已经创建。

（1）创建主控组对象以下为引用的内容：

CONNECT repadmin/repadmin@cl.world
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => ’inte_repg’);
END;
　
（2）向主控组中添加数据对象，将测区表survey加入到组inte_repg中以下为引用的内容：

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’inte_repg’,
type => ’TABLE’,
oname => ’survey’,
sname => ’integration’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;

以同样的方法将测线表line加入到组inte_repg中。

（3）在主控组中添加其他参与复制的站点，数据库之间的同步方式在此指定以下为引用的内容：

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ’inte_repg’,
master => ’js.world’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ’ASYNCHRONOUS’);
END;

（4）如果可能出现冲突，则需要配置冲突解决方案。

（5）为每个对象生成复制支持,以下为引用的内容：

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’integration’,
oname => ’survey’,
type => ’TABLE’,
min_communication => TRUE);
END;

测线表line也一样。
（6）重新开始复制以下为引用的内容：

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ’inte_repg’);
END;

以同样的方式设置解释站点。设置成功后，数据复制过程就宣告完毕，库中的数据就可进行复制。

两台oracle10g数据库,如何才能进行数据同步

如何实现Oracle数据库之间的数据同步

实现Oracle数据库的读写分离的方法：

DSG RealSync同步软件的实现方案：

利数据库同步复制软件是实施关键系统灾备工程的一个重要组成部分，当生产系统出现异常或故障时，备份系统的数据库能够完全代替生产系统的Oracle 数据库管理系统，以实现关键系统的正常运行。

1、充分利用投入的资源：

平时灾备数据库可以作为查询库来使用，方便的实现了读写分离，降低了主生产系统的压力。可以充分的把灾备资源利用起来，不至于浪费投入。

2、将查询、报表、统计等查询操作业务全部割接到备份数据库，主库只用于写操作。新写入的数据在三秒左右会同步到备份库。

读写分离的实现后，业务性能远远超过用户估计。

3、业务功能实现：

在关键业务应用系统的数据库上安装复制软件代理程序，通过代理程序获取数据库的交易，实现数据变化的实时跟踪。抓取的数据通过1000Mbps以太网进行实时传输，实现系统数据同步到备份系统上的实时传输。

4、技术实现：

复制软件是采用交易复制的方式进行数据同步；灾备数据库上的Oracle数据库处于OPEN状态，可提供实时数据访问。数据复制的时延可以控制在3秒左右。

两个oracle数据库个有部分数据要同步给对方怎么办?

用数据库快照就能实现啊.

Oracle快照原理及实现总结

Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。对于中大型数据库，业务数据库里所有的数据同步到另外一个处理服务器上最佳的选择还是使用SnapShot方式，即快照的方式。

由于工作需要，今天需要将业务数据库里所有的数据同步到另外一个处理服务器上。在做方案的时候，想了很多方法，当然最快的办法还是使用物理热备的方式。

但是我个人认为如果对于中大型数据库(我们的数据库有300G左右)最佳的选择还是使用SnapShot方式，即快照的方式。

Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。

以下是我建立的Snapshot，目的是从业务数据库上将数据Copy到处理数据库上，是不同的两个服务器之间对数据copy。

第一步：在处理服务器上的Oracle终端，建立database link，业务数据库服务器SID为TEST

create database link TEST_DBLINK.US.ORACLE.COM
connect to AMICOS identified by AMICOS
using 'test';

第二步：在业务数据库上对应的表建立快照日志
Create snapshot log on A_Table;

第三步：建立Snapshot 快照名称为：Test_SnapShot

Create snapshot Test_SnapShot
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24
as select * from A_Table@TEST_DBLINK

说明：REFRESH是刷新方法

刷新方式有：COMPLETE和FAST两种，而START WITH是说明开始执行的时间。

Next是下次执行的时间

而AS以后是构成快照的查询方法。

相关的方法：

更改快照

ALTER SNAPSHOT Test_SnapShot
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/2;

手动刷新快照在命令界面执行：

EXEC DBMS_SNAPSHOT.REFRESH('Test_SnapShot ','C');

第一个参数是要刷新的快照名

第二个参数是刷新的方式，F----FAST, C---COMPLETE

查看快照最后刷新的日期

SELECT NAME,LAST_REFRESH
FROM ALL_SNAPSHOT_REFRESH_TIMES;

最后非常的方案：

1：为需要做Snapshot的表建立Snapshot日志

create snapshot log on t1 with rowid; 这里使用ROWID建立日记的参数

2：采用Fast的方式建立快照，使用rowid做为参考参数

create snapshot fb_test_b refresh fast with rowid start with sysdate next sysdate+1/1440 as select * from fb_test_b@my_dblink;

最好能按照rowid来建立快照。要不然就必须要为表建立Primary Key。

http://blog.sina.com.cn/s/blog_59a515190100ayou.html

两个oracle数据库个有部分数据要同步给对方怎么办?

Oracle与SQL Server如何实现表数据同步

有时由于项目开发的需要，必须将SQLServer2005中的某些表同步到Oracle数据库中，由其他其他系统来读取这些数据。不同数据库类型之间的数据同步我们可以使用链接服务器和SQLAgent来实现。假设我们这边（SQLServer2005）有一个合同管理系统，其中有表contract 和contract_project是需要同步到一个MIS系统中的（Oracle9i）那么，我们可以按照以下几步实现数据库的同步。

1.在Oracle中建立对应的contract 和 contract_project表，需要同步哪些字段我们就建那些字段到Oracle表中。

这里需要注意的是Oracle的数据类型和SQLServer的数据类型是不一样的，那么他们之间是什么样的关系拉？我们可以在SQLServer下运行：

SELECT *

FROM msdb.dbo.MSdatatype_mappings

SELECT *

FROM msdb.dbo.sysdatatypemappings

来查看SQLServer和其他数据库系统的数据类型对应关系。第一个SQL语句是看SQL转Oracle的类型对应，而第二个表则更详细得显示了各个数据库系统的类型对应。根据第一个表和我们的SQLServer中的字段类型我们就可以建立好Oracle表了。

SQLServer和Oracle的对应

2.建立链接服务器。我们将Oracle系统作为SQLServer的链接服务器加入到SQLServer中。

具体做法参见我以前的文章http://www.cnblogs.com/studyzy/archive/2006/12/08/690307.html

3.使用SQL语句通过链接服务器将SQLServer数据写入Oracle中。

比如我们建立了链接服务器MIS，而Oracle中在MIS用户下面建立了表contract_project，那么我们的SQL语句就是：

DELETE FROM MIS..MIS.CONTRACT_PROJECT

--清空Oracle表中的数据

INSERT into MIS..MIS.CONTRACT_PROJECT--将SQLServer中的数据写到Oracle中

SELECT contract_id,project_code,actual_money

FROM contract_project

如果报告成功，那么我们的数据就已经写入到Oracle中了。用

SELECT *

FROM MIS..MIS.CONTRACT_PROJECT

查看Oracle数据库中是否已经有数据了。

4.建立SQLAgent，将以上同步SQL语句作为执行语句，每天定时同步两次。

这样我们的同步就完成了。

这里需要注意的是MIS..MIS.CONTRACT_PROJECT 这里必须要大写，如果是小写的话会造成同步失败。

如何实现oracle两个数据库之间的同步

现在主要的方法有以下三种：

oracle dataguard（数据库本身支持）

oracle goldengate(第三方)

oracle rac（共用存储情况下）