oracle建立dblink以及建立定时任务

最近在弄一个事，具体是我们要定时插数据到别人的数据库的中间表里

首先要做的是建立一个到对方数据库的oracle dblink，这是为了可以访问他们的数据库，这样我们才能将数据插入到他们中间表。所以我们需要他们中间表的数据库的IP、用户、密码、中间表的表名，具体可以查看数据库安装路径下的\product\10.2.0\client_1\network\admin\tnsnames.ora文件

例如：

TestDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.27.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB)         TESTDB -------- 实例名
    )
  )

在PL/SQL工具里输入：

create database link link名称 

connect to 对方数据库用户名 identified by 对方数据库用户密码
using '对方数据库ip:端口/实例名';

例如：

create database link DLTEST
connect to PMS identified by PMS
using '176.2.39.25:1521/TESTDB';

可用   select * from dual@DLTEST;   来测试是否连接成功

通过select * from 表名@DLTEST; 来访问该数据库表

这样，我们的oracle dblink就创建成功了，接下来需要创建一个定时任务来每天把自己的表数据插入到对方的中间表里。

查询该dblink下数据库的所有表：select table_name from user_tables@DLTEST;

删除dblink：DROP PUBLIC DATABASE LINK DLTEST;

--------------------------------------------------------------------------

这个时候oracle的定时执行任务JOB无疑是我的最佳选择。我把同步的脚本放在一个存储过程中，然后在固定的时间去执行这个存储过程就OK了。

例如：

CREATE OR REPLACE PROCEDURE dingshitest_proc  IS
BEGIN
  INSERT INTO 表名@DLTEST select * from 自己表;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
    ROLLBACK;
END;

接下来定制定时任务，

假设我们要每隔两分钟向表  表名@DLTEST  中插入一条记录，这里我们只需要每隔2分钟执行一次存储过程dingshitest_proc即可。

在PL/SQL的command window输入以下语句：

SQL> var job_num number;
SQL> begin
  2  dbms_job.submit(:job_num,'dingshitest_proc;',SYSDATE,'sysdate+1/24/60');
  3  end;
  4  /                       ------------以/作为结束

执行完后一定要记得点击提交按钮，

查看定时任务：select * from dba_jobs a where a.WHAT like '%dingshitest_proc%';

删除定时任务：在命令窗口下执行SQL> exec dbms_job.remove(961);

下面来讲讲定时任务的时间间隔怎么算的。

第一种调度任务需求的日期算法比较简单，即'SYSDATE+n',这里n是一个以天为单位的时间间隔。

描述	Interval参数值
每天运行一次	'SYSDATE + 1'
每小时运行一次	'SYSDATE + 1/24'
每10分钟运行一次	'SYSDATE + 10/（60*24）'
每30秒运行一次	'SYSDATE + 30/(60*24*60)'
每隔一星期运行一次	'SYSDATE + 7'
不再运行该任务并删除它	NULL

第二种调度任务需求相对于第一种就需要更复杂的时间间隔（interval）表达式

描述	INTERVAL参数值
每天午夜12点	'TRUNC(SYSDATE + 1)'
每天早上8点30分	'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点	'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点	'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点	'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分	'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'