job+dblink实现数据同步

来源：互联网发布：装饰装修施工资质知乎编辑：程序博客网时间：2024/06/05 16:10

时过境迁，当下已经不再鼓励使用这种方式了，仅作记录而已(四川信息产项目)

1.创建DBLINK　
create database link dblink_10000system
connect to primeton identified by primeton123
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =221.236.7.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sctsioms)
)
)';
查询：select * from dba_objects where object_type='DATABASE LINK'
关于通过DBLINK　访问对象方式：select count(*) from cstep@dblink_10000system

2.根据视图创建空表
3.创建创存储过程，存储过程调用备份SQL：
CREATE OR REPLACE PROCEDURE SCTSIOMS.PROCDURE_10000SYSTEM
as
begin
delete CSTEP_BAK;
commit;
insert into CSTEP_BAK　(STEP_ID,FLOW_ID,STEP_NAME,KEY_STEP,PASS_STEP_CONDITION,PREV_STEP_ID,NEXT_STEP_ID,POST_ADD,STEP_TYPE,INTERFACE_ID,IS_EXEC_STEP,DEFAULT_COLUMN1,DEFAULT_COLUMN2,DEFAULT_COLUMN3)
SELECT STEP_ID,FLOW_ID,STEP_NAME,KEY_STEP,PASS_STEP_CONDITION,PREV_STEP_ID,NEXT_STEP_ID,POST_ADD,STEP_TYPE,INTERFACE_ID,IS_EXEC_STEP,DEFAULT_COLUMN1,DEFAULT_COLUMN2,DEFAULT_COLUMN3 FROM CSTEP@dblink_10000system;
insert into PRO_TIMEEXC (CODE,NOWDATE) values ('PROCDURE_10000SYSTEM',sysdate);
commit;
end procdure_10000system;

日志表：PRO_TIMEEXC
        CREATE TABLE PRO_TIMEEXC(
         CODE VARCHAR2(100)
         NOWDATE DATE;
        );
4.创建JOB，定时调用存储过程：
variable job10000system number;
begin
dbms_job.submit(:job10000system,'PROCDURE_10000SYSTEM;',sysdate,'sysdate+1/1440');//每天1440分钟，即一分钟运行PROCDURE_10000SYSTEM过程一次　TRUNC(sysdate) + 1 +2 / (24)　每天零程两点执行
end;

执行JOB
begin
dbms_job.run(:job10000system);
end;

删除JOB
begin
dbms_job.remove(:job10000system);
end;

日志表：PRO_TIMEEXC
        CREATE TABLE PRO_TIMEEXC(
         NOWDATE DATE;
        );