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

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