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 //查询数据是否已经同步过来

相关文章