数据库表数据定期同步，其实有很多工具，如kettle， 当然，如果需要增量，可以在表中加入插入时间和更新时间，并根据此增量同步表数据。有时候是为了备份或者临时使用，表的数据量也不大，我们就可以定期删除再插入。 这种灵活方便。创建一个记录同步结果的表，创建存储过程，创建一个job，就好了。 下面是具体命令参考： 环境准备， ``` sql --创建一个记录表 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) ) )'; ``` 创建存储过程，注意，存储过程中一些权限需要单独赋权，如创建表的权限 ``` sql --赋权 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分钟重建一次表 ``` sql --创建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; / ``` 检查同步信息 ``` sql --检查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; ``` 后期，会通过更新时间，同步时间 进行增量同步