使用DM資料庫，建立一個連線到IP地址為10.10.10.180機器上的oracle資料庫的外部連結。可以透過三種方式建立：一網路服務名tsn_name；二連線描述符description；三/< 服務名>。

（一）透過網路服務名建立
首先介紹Oracle網路服務名的配置方法。網路服務名配置成功才能建立DBLINK
安裝Oracle客戶端挺費時間的，而且大部分功能都用不到，Oracle官方給出了簡易客戶端，直接解壓就可以使用，下載地址：
下面來看安裝步驟：
1、下載安裝包，我這裡把所有的都下載下來了

 [root@dmks soft]# ls -lrt
總用量 2293896
drwxr-xr-x. 19 1000 1000       4096 4月  20 2010 unixODBC-2.3.0
drwxrwxr-x.  5  502  502       4096 1月  27 2017 client
-rw-r--r--.  1  502  502 1258314437 3月   2 2017 linuxx64_12201_client.zip
-rw-r--r--.  1 root root  493170688 3月  19 2018 dm7_setup_neokylin6_64_20180209.iso
-rw-r--r--.  1 root root  488814959 3月   4 15:19 DM_linux64.zip
drwxr-xr-x.  2 root root       4096 3月   6 19:47 dm_soft
-rw-r--r--.  1 root root    1804749 3月  11 17:20 unixODBC-2.3.0.tar.gz
-rw-r--r--.  1 root root     904309 3月  21 18:38 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
-rw-r--r--.  1 root root   68965195 3月  21 18:42 instantclient-basic-linux.x64-12.2.0.1.0.zip
-rw-r--r--.  1 root root     674743 3月  21 18:45 instantclient-sdk-linux.x64-12.2.0.1.0.zip
-rw-r--r--.  1 root root    1572942 3月  21 22:51 instantclient-jdbc-linux.x64-12.2.0.1.0.zip
-rw-r--r--.  1 root root     634023 3月  21 22:51 instantclient-odbc-linux.x64-12.2.0.1.0-2.zip
-rw-r--r--.  1 root root   32917466 3月  21 22:51 instantclient-basiclite-linux.x64-12.2.0.1.0.zip
-rw-r--r--.  1 root root    1132671 3月  21 22:52 instantclient-tools-linux.x64-12.2.0.1.0.zip

2、unzip解壓
解壓出來一個目錄instantclient_12_2

[root@dmks soft]# unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/glogin.sql
  inflating: instantclient_12_2/libsqlplusic.so
  inflating: instantclient_12_2/libsqlplus.so
  inflating: instantclient_12_2/sqlplus
  inflating: instantclient_12_2/SQLPLUS_README
[root@dmks soft]# unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-basic-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/adrci
  inflating: instantclient_12_2/BASIC_README
  inflating: instantclient_12_2/genezi
  inflating: instantclient_12_2/libclntshcore.so.12.1
  inflating: instantclient_12_2/libclntsh.so.12.1
  inflating: instantclient_12_2/libipc1.so
  inflating: instantclient_12_2/libmql1.so
  inflating: instantclient_12_2/libnnz12.so
  inflating: instantclient_12_2/libocci.so.12.1
  inflating: instantclient_12_2/libociei.so
  inflating: instantclient_12_2/libocijdbc12.so
  inflating: instantclient_12_2/libons.so
  inflating: instantclient_12_2/liboramysql12.so
  inflating: instantclient_12_2/ojdbc8.jar
  inflating: instantclient_12_2/uidrvci
  inflating: instantclient_12_2/xstreams.jar
[root@dmks soft]# unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-sdk-linux.x64-12.2.0.1.0.zip
   creating: instantclient_12_2/sdk/
  inflating: instantclient_12_2/sdk/ott
 extracting: instantclient_12_2/sdk/ottclasses.zip
  inflating: instantclient_12_2/sdk/SDK_README
   creating: instantclient_12_2/sdk/demo/
  inflating: instantclient_12_2/sdk/demo/setuporamysql.sh
  inflating: instantclient_12_2/sdk/demo/occiobj.typ
  inflating: instantclient_12_2/sdk/demo/occidml.cpp
  inflating: instantclient_12_2/sdk/demo/occidemo.sql
  inflating: instantclient_12_2/sdk/demo/occiobj.cpp
  inflating: instantclient_12_2/sdk/demo/occidemod.sql
  inflating: instantclient_12_2/sdk/demo/oraaccess.xml
  inflating: instantclient_12_2/sdk/demo/demo.mk
  inflating: instantclient_12_2/sdk/demo/cdemo81.c
   creating: instantclient_12_2/sdk/include/
  inflating: instantclient_12_2/sdk/include/occiControl.h
  inflating: instantclient_12_2/sdk/include/oro.h
  inflating: instantclient_12_2/sdk/include/ociapr.h
  inflating: instantclient_12_2/sdk/include/occiCommon.h
  inflating: instantclient_12_2/sdk/include/occiData.h
  inflating: instantclient_12_2/sdk/include/oci8dp.h
  inflating: instantclient_12_2/sdk/include/ociextp.h
  inflating: instantclient_12_2/sdk/include/orl.h
  inflating: instantclient_12_2/sdk/include/nzt.h
  inflating: instantclient_12_2/sdk/include/ldap.h
  inflating: instantclient_12_2/sdk/include/occi.h
  inflating: instantclient_12_2/sdk/include/ociap.h
  inflating: instantclient_12_2/sdk/include/odci.h
  inflating: instantclient_12_2/sdk/include/ocixstream.h
  inflating: instantclient_12_2/sdk/include/nzerror.h
  inflating: instantclient_12_2/sdk/include/oci1.h
  inflating: instantclient_12_2/sdk/include/ori.h
  inflating: instantclient_12_2/sdk/include/ocixmldb.h
  inflating: instantclient_12_2/sdk/include/ocidem.h
  inflating: instantclient_12_2/sdk/include/occiAQ.h
  inflating: instantclient_12_2/sdk/include/ocidef.h
  inflating: instantclient_12_2/sdk/include/occiObjects.h
  inflating: instantclient_12_2/sdk/include/oci.h
  inflating: instantclient_12_2/sdk/include/oratypes.h
  inflating: instantclient_12_2/sdk/include/orid.h
  inflating: instantclient_12_2/sdk/include/xa.h
  inflating: instantclient_12_2/sdk/include/ocikpr.h
  inflating: instantclient_12_2/sdk/include/ocidfn.h
  inflating: instantclient_12_2/sdk/include/ort.h
   creating: instantclient_12_2/sdk/admin/
  inflating: instantclient_12_2/sdk/admin/oraaccess.xsd
[root@dmks soft]# unzip instantclient-jdbc-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-jdbc-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/JDBC_README
  inflating: instantclient_12_2/libheteroxa12.so
  inflating: instantclient_12_2/orai18n.jar
  inflating: instantclient_12_2/orai18n-mapping.jar
[root@dmks soft]# unzip instantclient-odbc-linux.x64-12.2.0.1.0-2.zip
Archive:  instantclient-odbc-linux.x64-12.2.0.1.0-2.zip
   creating: instantclient_12_2/help/
   creating: instantclient_12_2/help/ja/
  inflating: instantclient_12_2/help/ja/blafdoc.css
  inflating: instantclient_12_2/help/ja/oracle.gif
   creating: instantclient_12_2/help/ja/img_text/
  inflating: instantclient_12_2/help/ja/img_text/setup_ssmig.htm
  inflating: instantclient_12_2/help/ja/img_text/setup_work.htm
  inflating: instantclient_12_2/help/ja/img_text/odbcmodel.htm
  inflating: instantclient_12_2/help/ja/img_text/setup_app.htm
  inflating: instantclient_12_2/help/ja/img_text/setup_ora.htm
  inflating: instantclient_12_2/help/ja/img_text/odbcdrvarch.htm
  inflating: instantclient_12_2/help/ja/toc.htm
  inflating: instantclient_12_2/help/ja/map.xml
   creating: instantclient_12_2/help/ja/META-INF/
  inflating: instantclient_12_2/help/ja/META-INF/MANIFEST.MF
   creating: instantclient_12_2/help/ja/img/
  inflating: instantclient_12_2/help/ja/img/setup_app.gif
  inflating: instantclient_12_2/help/ja/img/odbcmodel.gif
  inflating: instantclient_12_2/help/ja/img/setup_ora.gif
  inflating: instantclient_12_2/help/ja/img/odbcdrvarch.gif
  inflating: instantclient_12_2/help/ja/img/setup_ssmig.gif
  inflating: instantclient_12_2/help/ja/img/setup_work.gif
  inflating: instantclient_12_2/help/ja/cpyr.htm
  inflating: instantclient_12_2/help/ja/sqora.htm
   creating: instantclient_12_2/help/us/
  inflating: instantclient_12_2/help/us/blafdoc.css
  inflating: instantclient_12_2/help/us/oracle.gif
   creating: instantclient_12_2/help/us/img_text/
  inflating: instantclient_12_2/help/us/img_text/setup_ssmig.htm
  inflating: instantclient_12_2/help/us/img_text/setup_work.htm
  inflating: instantclient_12_2/help/us/img_text/odbcmodel.htm
  inflating: instantclient_12_2/help/us/img_text/setup_app.htm
  inflating: instantclient_12_2/help/us/img_text/setup_ora.htm
  inflating: instantclient_12_2/help/us/img_text/odbcdrvarch.htm
  inflating: instantclient_12_2/help/us/toc.htm
  inflating: instantclient_12_2/help/us/map.xml
   creating: instantclient_12_2/help/us/META-INF/
  inflating: instantclient_12_2/help/us/META-INF/MANIFEST.MF
   creating: instantclient_12_2/help/us/img/
  inflating: instantclient_12_2/help/us/img/setup_app.gif
  inflating: instantclient_12_2/help/us/img/odbcmodel.gif
  inflating: instantclient_12_2/help/us/img/setup_ora.gif
  inflating: instantclient_12_2/help/us/img/odbcdrvarch.gif
  inflating: instantclient_12_2/help/us/img/setup_ssmig.gif
  inflating: instantclient_12_2/help/us/img/setup_work.gif
  inflating: instantclient_12_2/help/us/cpyr.htm
  inflating: instantclient_12_2/help/us/sqora.htm
  inflating: instantclient_12_2/libsqora.so.12.1
  inflating: instantclient_12_2/ODBC_IC_Readme_Unix.html
  inflating: instantclient_12_2/odbc_update_ini.sh
[root@dmks soft]# unzip instantclient-basiclite-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-basiclite-linux.x64-12.2.0.1.0.zip
replace instantclient_12_2/adrci? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: instantclient_12_2/adrci
  inflating: instantclient_12_2/BASIC_LITE_README
  inflating: instantclient_12_2/genezi
  inflating: instantclient_12_2/libclntshcore.so.12.1
  inflating: instantclient_12_2/libclntsh.so.12.1
  inflating: instantclient_12_2/libipc1.so
  inflating: instantclient_12_2/libmql1.so
  inflating: instantclient_12_2/libnnz12.so
  inflating: instantclient_12_2/libocci.so.12.1
  inflating: instantclient_12_2/libociicus.so
  inflating: instantclient_12_2/libocijdbc12.so
  inflating: instantclient_12_2/libons.so
  inflating: instantclient_12_2/liboramysql12.so
  inflating: instantclient_12_2/ojdbc8.jar
  inflating: instantclient_12_2/uidrvci
  inflating: instantclient_12_2/xstreams.jar
[root@dmks soft]# unzip instantclient-tools-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-tools-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/exp
  inflating: instantclient_12_2/expdp
  inflating: instantclient_12_2/imp
  inflating: instantclient_12_2/impdp
  inflating: instantclient_12_2/libnfsodm12.so
  inflating: instantclient_12_2/sqlldr
  inflating: instantclient_12_2/TOOLS_README
  inflating: instantclient_12_2/wrc

3、配置環境變數

export ORACLE_HOME=/soft/instantclient_12_2
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
export PATH=$ORACLE_HOME:$PATH

4、配置tns

[root@dmks instantclient_12_2]# mkdir -p network/admin
[root@dmks instantclient_12_2]# cd  network/admin/
[root@dmks admin]# vi tnsnames.ora
shardcat =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.180)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = shardcat)
    )
  )

5、測試sqlplus，成功

[root@dmks admin]# sqlplus jy/xxzx7817600@shardcat
SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 21 23:07:41 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Sun Mar 22 2020 01:36:51 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

複製動態庫檔案

[root@dmks instantclient_12_2]# cp  *so*  /dm_home/dmdbms/bin/
[root@dmks instantclient_12_2]# cd  /dm_home/dmdbms/bin/
[root@dmks bin]# chown -R dmdba:dinstall *so*  //一定要將動態庫檔案修改為dmdba使用者所有
[root@dmks bin]# chmod -R 777 /dm_home/dmdbms/bin/

建立DBlink
網路服務名配置成功後，就可以使用網路服務名shardcat或網路連線描述符建立DBLINK.

SQL> create or replace public link link3 connect 'oracle' with system identified by "xxzx7817600" using 'shardcat';
executed successfully
used time: 27.431(ms). Execute id is 8.
SQL> select * from v$version@link3;
LINEID     BANNER                                                                       CON_ID
---------- ---------------------------------------------------------------------------- ------
1          Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2          PL/SQL Release 12.2.0.1.0 - Production                                       0
3          CORE 12.2.0.1.0 Production                                                   0
4          TNS for Linux: Version 12.2.0.1.0 - Production                               0
5          NLSRTL Version 12.2.0.1.0 - Production                                       0
used time: 66.846(ms). Execute id is 9.

（二）透過連線描述符建立

SQL> create or replace public link link4 connect 'oracle' with system identified by "xxzx7817600" using '(description =
2   (address_list = (address = (protocol = tcp)(host = 10.10.10.180)(port = 1521)))
3   (connect_data = (service_name = shardcat)))';
executed successfully
used time: 19.074(ms). Execute id is 10.
SQL> select * from v$version@link4;
LINEID     BANNER                                                                       CON_ID
---------- ---------------------------------------------------------------------------- ------
1          Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2          PL/SQL Release 12.2.0.1.0 - Production                                       0
3          CORE 12.2.0.1.0 Production                                                   0
4          TNS for Linux: Version 12.2.0.1.0 - Production                               0
5          NLSRTL Version 12.2.0.1.0 - Production                                       0
used time: 140.930(ms). Execute id is 12.

（三）透過/< 服務名>建立

SQL> create or replace public link link5 connect 'oracle' with system identified by "xxzx7817600" using '10.10.10.180/shardcat';
executed successfully
used time: 17.347(ms). Execute id is 13.
SQL> select * from v$version@link5;
LINEID     BANNER                                                                       CON_ID
---------- ---------------------------------------------------------------------------- ------
1          Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2          PL/SQL Release 12.2.0.1.0 - Production                                       0
3          CORE 12.2.0.1.0 Production                                                   0
4          TNS for Linux: Version 12.2.0.1.0 - Production                               0
5          NLSRTL Version 12.2.0.1.0 - Production                                       0
used time: 92.869(ms). Execute id is 14.
SQL> select * from jy.t1@link3;
LINEID     T_ID T_NAME
---------- ---- -------
1          9    m_YYL
2          1    m_A
3          2    m_B
4          3    m_C
5          4    m_D
6          5    m_E
7          6    m_F
8          7    m_JYHY
9          8    m_JYYYL
9 rows got
used time: 10.891(ms). Execute id is 15.
SQL> insert into jy.t1@link3 values(9,'m_wy');
affect rows 1
used time: 33.658(ms). Execute id is 16.
SQL> commit;
executed successfully
used time: 20.563(ms). Execute id is 17.
SQL> select * from jy.t1;
      T_ID T_NAME
---------- ----------------------------------------
         9 m_wy
         9 m_YYL
         1 m_A
         2 m_B
         3 m_C
         4 m_D
         5 m_E
         6 m_F
         7 m_JYHY
         8 m_JYYYL
10 rows selected.

刪除外部連結
刪除一個外部連結。
語法格式
DROP [PUBLIC] LINK [< 模式名>.]< 外部連結名>;
引數
1．< 模式名> 指明被操作的外部連結屬於哪個模式，預設為當前模式；
2．< 外部連結名> 指明被操作的外部連結的名稱。

語句功能
刪除一個外部連結。
使用說明
只有連結物件的建立者和DBA擁有該物件的刪除許可權。

舉例說明
刪除外部連結LINK1。
DROP LINK LINK1;

使用外部連結透過外部連結，可以對遠端伺服器的物件進行查詢或進行增刪改操作，可以呼叫遠端的過程。使用外部連結進行查詢或增刪改的語法格式與普通格式基本一致，唯一的區別在於指定外部連結表時需要使用如下格式作為表或檢視的引用：
[TABLENAME | VIEWNAME] [LINK | @] 連結名

舉例說明使用外部連結查詢LINK1上的遠端表進行查詢
SELECT * FROM SYSOBJECTS LINK LINK1; 或對遠端表進行插入資料：
INSERT INTO T1@LINK1 VALUES(1,2,3);
也可以查詢本地表或其他連結的表對遠端表進行操作，如
UPDATE T1@LINK1 SET C1 = C1+1 WHERE C2 NOT IN (SELECT ID FROM LOCAL_TABLE);
DELETE FROM T1@LINK1 WHERE C1 IN (SELECT ID FROM T2@LINK2);

使用外部連結，可以呼叫遠端的儲存過程，但是不支援呼叫遠端的函式，使用中有以下約束：
（1）引數資料型別為SQL型別，不允許為DMSQL程式型別；
（2）引數資料型別不允許為複合型別。
其使用方式為：
[CALL] [< 模式名>.][< 包名>.]< 過程名> [@] < 外部連結名>(< 引數列>);

使用限制
外部連結的使用有以下限制：
1. DM-DM的同構外部連結不支援MPP環境，DM與異構資料庫的外部連結支援MPP環境；
2. 增刪改不支援INTO語句；
3. 不支援使用遊標進行增刪改操作；
4. DBLINK理論上不支援LOB型別列的操作，但支援簡單的增刪改語句中使用常量來對LOB型別列進行操作。
另外，DM連線異構資料庫的外部連結還有如下使用限制：
1. 資料型別以DM為基礎，不支援DM沒有的資料型別；
2. 語法以DM的語法為標準，不支援DM不相容的語法；
3. 主鍵更新，如果是涉及到多個伺服器的語句，不能保證更新操作一定成功。

達夢7異構(DM-Oracle) DBLINK

相關文章