oracle建立dblink以及建立定時任務，oracle建立dblink

oracle建立dblink以及建立定時任務，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 LINKDLTEST;

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

這個時候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）'