Oracle資料庫通過DBLINK實現遠端存取

Oracle資料庫通過DBLINK實現遠端存取

什麼是DBLINK?

dblink(Database Link)資料庫連結顧名思義就是資料庫的連結  ，就像電話線一樣，是一個通道，當我們要跨本機資料庫，訪問另外一個資料庫表中的資料時，本機資料庫中就必須要建立遠端資料庫的dblink,通過dblink本機資料庫可以像訪問本機資料庫一樣訪問遠端資料庫表中的資料。

如何使用DBLINK?

情境：假設當前資料庫使用者為ALANLEE，此時需要通過ALANLEE這個使用者去採集遠端資料庫的資料。

遠端資料庫資訊如下：

HSAJ216 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 12.1.3.216)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hscsserver)
    )
  )

遠端資料庫使用者名稱：hs_user，密碼：hundsun

第一步：查看使用者是否具備建立database link許可權

--查看ALANLEE使用者是否具備建立database link許可權
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='ALANLEE';
select * from user_sys_privs t where t.privilege like upper('%link%');

在資料庫中dblink有這麼一些許可權。例如CREATE DATABASE LINK表示所建立的dblink只能是建立者能使用，別的使用者使用不了，CREATE PUBLIC DATABASE LINK表示所建立的dblink所有使用者都可以使用，DROP PUBLIC DATABASE LINK表示刪除公用dblink的許可權。

假如查出相關的資料則表示ALANLEE使用者具有相關的許可權，如果沒有查出資料則說明ALANLEE使用者沒有相關的許可權。

第二步：假如使用者不具備相應的許可權則需要授權，如果ALANLEE使用者具有類似管理使用者授權的許可權則直接使用目前使用者授權，如果ALANLEE不具備這樣的許可權則使用SYS/SYSTEM之類許可權更大的資料庫使用者來給ALANLEE使用者授權

--需要授予ALANLEE使用者建立資料庫連結許可權
grant create public database link to ALANLEE;
--需要授予ALANLEE使用者刪除資料庫連結許可權
grant drop public database link to ALANLEE;

這裡我們使用公用的dblink，即所有使用者都可以使用的dblink，可以根據自己的所需去賦予相應的許可權，授權成功後可以通過第一步的視圖查看是否授權成功。

第三步：通過ALANLEE使用者建立遠端資料庫連結（資料庫地址：12.1.3.216 使用者名稱：hs_user 密碼：hundsun）

drop public database link HSAJ216;
create public database link HSAJ216
connect to hs_user identified by hundsun
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 12.1.3.216)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = hscsserver)))';

為了避免一些其他的問題，這裡建議直接使用遠端資料庫的全域監聽執行個體名作為database link的名稱，也就是遠端資料庫資訊所示的HSAJ216。

第四步：查詢已經建立的資料庫遠程連結

select owner,object_name from dba_objects where object_type='DATABASE LINK';

如果有自己建立的database link資料則說明建立成功，反之就是不存在。

第五步：測試建立的遠端資料庫連結

select * from dual@HSAJ216;

如果能查出東西，則遠端存取便成功了。

如何查詢遠端資料庫某個使用者某個表的資料呢？sql如下：

select * from hs_asset.client@HSAJ216;

如果能查詢出表的資料，那就可以開始去做資料擷取的工作了，查詢出相應的資料，插入本機資料庫的表中。

最後一步：通過預存程序採集遠端資料庫的資料並插入到本地的資料庫當中

/**
*從櫃檯同步客戶資料至暫存資料表
*/
create or replace PROCEDURE SP_SYNC_CUSTOMER_TEMP (UPDATE_TOTAL OUT NUMBER) IS
  INDEX_COUNT NUMBER;
  INDEX_TOTAL NUMBER;
  CURSOR CR IS
    select a.client_id, --客戶編碼
          a.branch_no, --分公司
          a.id_no, --證件號碼
          a.client_name, --客戶姓名
          a.client_status, --客戶狀態
          a.open_date, --開戶日期
          c.fund_account, --資金帳號
          c.main_flag, --主賬標識
          c.asset_prop, --資產屬性
          b.birthday, --生日日期
          b.address, --地址
          b.home_tel, --家庭電話
          b.e_mail, --郵箱
          b.fax, --傳真
          b.mobile_tel, --手機號碼
          b.office_tel, --單位電話
          b.zipcode, --郵遞區號
          b.account_data --開戶規範資訊
      from hs_asset.client@HSAJ216 a
    inner join (select client_id,
                        birthday,
                        address,
                        home_tel,
                        e_mail,
                        fax,
                        mobile_tel,
                        office_tel,
                        zipcode,
                        account_data
                  from hs_asset.clientinfo@HSAJ216
                union all
                select client_id,
                        '19000101' as birthday,
                        address,
                        contact_tel as home_tel,
                        e_mail,
                        fax,
                        mobile_tel,
                        contact_tel as office_tel,
                        zipcode,
                        'A' as account_data
                  from hs_asset.organinfo@HSAJ216) b
        on a.client_id = b.client_id
    inner join hs_asset.fundaccount@HSAJ216 c
        on a.client_id = c.client_id
    where c.asset_prop = '0';
BEGIN
  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, 'START-現在開始執行【SP_SYNC_CUSTOMER_TEMP】清空客戶同步暫存資料表TEMP_SYNC_CUSTOMER...');
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SYNC_CUSTOMER';
  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, 'OVER-【SP_SYNC_CUSTOMER_TEMP】清空客戶同步暫存資料表TEMP_SYNC_CUSTOMER已完成...');

  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, 'START-現在開始執行【SP_SYNC_CUSTOMER_TEMP】向客戶同步暫存資料表匯入資料TEMP_SYNC_CUSTOMER...');
  commit;

  INDEX_COUNT := 1;
  INDEX_TOTAL := 0;
  FOR C IN CR LOOP
    --客戶暫存資料表
    INSERT INTO TEMP_SYNC_CUSTOMER
      (CODE,
      ORGA_ID,
      ID_CARD,
      NAME,
      CLOSE_STATUS,
      ACCOUNT_CREATE_DATE,
      CAPITAL_ACCOUNT,
      BIRTHDAY,
      ADDRESS,
      TEL,
      BINDING_EMAIL,
      BINDING_MOBILE,
      MAIN_FLAG
      )
    VALUES
      (C.client_id,
      C.branch_no,
      C.id_no,
      C.client_name,
      C.client_status,
      C.open_date,
      C.fund_account,
      C.birthday,
      C.address,
      C.home_tel,
      C.e_mail,
      C.mobile_tel,
      C.main_flag);
     
    INDEX_COUNT := (INDEX_COUNT + 1);
    INDEX_TOTAL := (INDEX_TOTAL + 1);
   
    IF INDEX_COUNT > 100000 THEN
      COMMIT;
      insert into t_coll_result (id, CREATE_DATE, REMARK)
      values (seq_t_coll_result_id.nextval, sysdate, '【SP_SYNC_CUSTOMER_TEMP】已向TEMP_SYNC_CUSTOMER匯入' || INDEX_TOTAL || '條資料...');
      commit;
      INDEX_COUNT := 1;
    END IF;
  END LOOP;

  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, 'OVER-【SP_SYNC_CUSTOMER_TEMP】同步客戶暫存資料表TEMP_SYNC_CUSTOMER已完成，共匯入' || INDEX_TOTAL || '條資料...');
  UPDATE_TOTAL := INDEX_TOTAL;
  COMMIT;
END SP_SYNC_CUSTOMER_TEMP;

當然，我們不可能每次都手動去執行sql，所以可以結合Oracle資料庫的定時任務，在每天的某個時刻自動去執行我們所寫的預存程序，這樣就相對來說比較完美了。

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151426.htm