使用DM数据库，创建一个连接到IP地址为10.10.10.180机器上的oracle数据库的外部链接。可以通过三种方式创建：一网络服务名tsn_name；二连接描述符description；三
（一） 通过网络服务名创建
首先介绍Oracle网络服务名的配置方法。网络服务名配置成功才能创建DBLINK
安装Oracle客户端挺费时间的，而且大部分功能都用不到，Oracle官方给出了简易客户端，直接解压就可以使用，下载地址：http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
下面来看安装步骤：
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. 主键更新，如果是涉及到多个服务器的语句，不能保证更新操作一定成功。