達夢7異構(DM-Oracle) DBLINK
使用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. 主鍵更新，如果是涉及到多個伺服器的語句，不能保證更新操作一定成功。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2687109/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 配置ORACLE資料庫到達夢資料庫的異構DBLINKOracle資料庫
- 配置達夢資料庫同構DBLINK資料庫
- 達夢DBlink叢集之間通訊搭建
- Redhat 7 下安裝達夢7Redhat
- 達夢7資料庫初始化資料庫
- 達夢6.0試用之架構篇架構
- 達夢7在Linux平臺安裝Linux
- 關於ORACEL與異構資料庫之間建立dblink資料庫
- 達夢資料庫基礎知識（三）達夢資料庫記憶體結構資料庫記憶體
- 達夢資料庫索引結構詳解資料庫索引
- 達夢DIsqlSQL
- 達夢安裝
- 達夢遷移工具之MySQL資料庫遷移到達夢MySql資料庫
- Oracle Linux 7.1中安裝達夢資料庫DM7OracleLinux資料庫
- 達夢DM7 資料庫之資料守護DG搭建資料庫
- 達夢DMDSC叢集搭建
- 達夢6.0 for Windows安裝Windows
- 達夢資料庫開發資料庫
- 達夢資料庫安裝資料庫
- 初識達夢資料庫資料庫
- 達夢SQL優化方法statSQL優化
- 達夢資料庫學習資料庫
- 達夢6.0試用之PLSQL篇SQL
- 達夢6.0試用之SQL篇SQL
- 自主研發“武漢造”異軍突起——達夢資料庫打造“中國版甲骨文”資料庫
- 在異機上利用dblink做impdp資料匯入
- 小兔子異世界冒險開啟 《異幻之夢》3月7日發售
- 達夢dmfldr資料快速載入
- 達夢dmfldr載入大欄位
- 達夢列儲存表(HUGE Table)
- 效能優化之達夢AWR使用優化
- 達夢資料庫基礎---表資料庫
- 達夢資料庫日常運維資料庫運維
- 達夢資料庫學習心得資料庫
- 達夢6.0試用之資料字典
- 達夢資料庫基礎知識（二）資料庫邏輯結構資料庫
- 達夢資料庫備份恢復資料庫
- 達夢資料庫之初步測試資料庫