Oracle 通过dblink和job方式实现两个数据库表之间数据同步

时间:2021-07-19 14:38:04

需求是需要将Database_A中的dev_test表中的数据同步到Database_B中的dev_test表中。

因为是通过Database_B去同步Database_A库中的数据,所以操作都建立在Database_B中,这样不会对Database_A做任何修改。业务应用场景主要是需要拉取第三方数据库中的数据。下面所有脚本都是建立在Database_B中

第一步:建立DBLink

在Database_B中建立DBLink;

本文是通过pl/sql来编写的脚本;

create database link dblink_datapull
connect to 对方数据库账号 identified by "对方数据库密码"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=对方数据库地址)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = 对方数据库实例名)
)
)'
select * from dual@dblink_datapull
//测试是否与Database_A联通
 create public synonym hw_test for dev_test@dblink_datapull
//通过语句对需要同步的表建立别名

 第二步:创建同步数据的存储过程

CREATE OR REPLACE PROCEDURE TEST_JOB_DataPull
AS SCOTT_ID NUMBER;
BEGIN
SELECT NVL (MAX (ID), 0) INTO SCOTT_ID FROM dev_test; INSERT INTO dev_test
SELECT *
FROM hw_test
WHERE hw_test.ID > SCOTT_ID; COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Exception happened,data was rollback');
ROLLBACK;
END;
//dev_test 是Database_B中的数据表
//hw_test 是针对Database_A中DBlink建立的别名

 第三步:创建定时任务

declare
job_num number;
begin
dbms_job.submit(job=>job_num,what=>'TEST_JOB_DataPull;',next_date=>SYSDATE,interval=>'sysdate+1/(60*24*60)');
commit;
end;
//what 需要执行的存储过程
//next_date 执行时间
//interval 执行时间间隔
SELECT * FROM USER_JOBS
//查看定时任务创建是否成功
oracle JOB常见的执行时间

1、每分钟执行
TRUNC(sysdate,'mi')+1/(24*60)
2、每天定时执行
例如:
每天凌晨0点执行
TRUNC(sysdate+1)
每天凌晨1点执行
TRUNC(sysdate+1)+1/24
每天早上8点30分执行
TRUNC(SYSDATE+1)+(8*60+30)/(24*60) 3、每周定时执行
例如:
每周一凌晨2点执行
TRUNC(next_day(sysdate,1))+2/24
TRUNC(next_day(sysdate,'星期一'))+2/24
每周二中午12点执行
TRUNC(next_day(sysdate,2))+12/24
TRUNC(next_day(sysdate,'星期二'))+12/24 4、每月定时执行
例如:
每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE)+1)
每月1日凌晨1点执行
TRUNC(LAST_DAY(SYSDATE)+1)+1/24 5、每季度定时执行
每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')
每季度的第一天凌晨2点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')+2/24
每季度的最后一天的晚上11点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24 6、每半年定时执行
例如:
每年7月1日和1月1日凌晨1点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),6)+1/24 7、每年定时执行
例如:
每年1月1日凌晨2点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),12)+2/24

 第四步:查看数据是否同步

select * from dev_test //查询数据是否已经同步过来