数据库表数据定期同步，其实有很多工具，如kettle，当然，如果需要增量，可以在表中加入插入时间和更新时间，并根据此增量同步表数据。有时候是为了备份或者临时使用，表的数据量也不大，我们就可以定期删除再插入。这种灵活方便。创建一个记录同步结果的表，创建存储过程，创建一个job，就好了。下面是具体命令参考：

环境准备，

--创建一个记录表
create table sync_table_log (tab_name varchar2(30),sync_time date,sync_log default ('sync success'));  
--创建dblink
CREATE database link mypdb
CONNECT TO mywork IDENTIFIED BY oracle
USING '(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.211)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = mypdb)
    )
)';

创建存储过程，注意，存储过程中一些权限需要单独赋权，如创建表的权限

--赋权
grant create on table to xxx;
--创建存储过程
create or replace procedure ss_table_sync
is
v_count number;
begin
select count(1) into v_count from mywork.sh2020@mypdb where rownum<=1;
if v_count = 1 then
execute immediate 'truncate table mytest.sh2020';
insert into mytest.sh2020 select * from mywork.sh2020@mypdb;
commit;
insert into mytest.sync_log(tab_name,sync_log) values('sh2020',sysdate);
commit;
end if;
Exception
   WHEN OTHERS
   then
   insert into mytest.sync_log values('sh2020-errors',sysdate,'sunc error');
end;
/

创建一个job，下面是10分钟重建一次表

--创建job
begin
  sys.dbms_scheduler.create_job(job_name            => 'sync_sh2022',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'ss_table_sync',
                                start_date          => to_date('2022/01/21 13:10:00','yyyy/mm/dd hh24:mi:ss'),
                                repeat_interval     => 'Freq=MINUTELY;Interval=2',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => 'sync table sh2020');
end;
/

检查同步信息

--检查job
set lines 200 pages 999
col job_name for a15
col program_name for a15
col schedule_name for a15
col start_date for a19
col repeat_interval for a24
col end_date for a19
col source for a10
col comments for a20
col LAST_START_DATE for a15
select job_name,PROGRAM_NAME,SCHEDULE_NAME,
to_char(START_DATE,'yyyy/mm/dd hh24:mi:ss') START_DATE,REPEAT_INTERVAL,END_DATE,LAST_START_DATE,SOURCE,comments from user_scheduler_jobs;
--检查同步日志
select * from sync_table_log order by 2 desc;

后期，会通过更新时间，同步时间进行增量同步

【SQL】Oracle数据库通过job定期重建同步表数据