dblink

 

SQL>  show parameter open_links
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

参数的解释：
***open_links :每个session最多允许的dblink数量;
***open_links_per_instance:指每个实例最多允许的dblink个数

创建 DBLINK 

CREATE PUBLIC DATABASE LINK DBL_BJLHR CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TNS_BJLHR';
SELECT * FROM SCOTT.TB_TEST@DBL_BJLHR;

CREATE DATABASE LINK DBL_BJLHR
CONNECT TO SCOTT IDENTIFIED BY TIGER
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BJLHR)
)
)';

查看dblink

select * from dba_db_links d WHERE D.db_link LIKE '%PROD2_2%';

Oracle通过dblink连接MySQL  

 参考文档： Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)

原理：

Oracle使用DG4ODBC数据网关连接其它非Oracle数据库，其原理图如下：

1）判断32位还是64位

因为32位和64位的配置不一样，64位更复杂一些，因此我们首先得确定Oracle和DG4ODBC是32位还是64位：

file $ORACLE_HOME/bin/dg4odbc  

2）下载并安装ODBC Driver Manager(默认已安装2.3.1版本)

http://www.unixodbc.org/

tar -zxvf unixODBC-2.3.9.tar.gz

cd unixODBC-2.3.9              (root执行)
./configure --prefix=/usr/local/unixODBC-2.3.9 --includedir=/usr/include --libdir=/usr/lib64 --bindir=/usr/bin --sysconfdir=/etc
make && make install

---unixODBC所需的头文件都被安装到了/usr/inlucde下，编译好的库文件安装到了/usr/lib64下，与unixODBC相关的可执行文件安装到了/usr/bin下，配置文件放到了/etc下。

执行命令：odbcinst -j  验证是否安装成功

3）下载并按照ODBC Driver for MySQL

https://downloads.mysql.com/archives/c-odbc/
tar -zxvf mysql-connector-odbc-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-connector-odbc-8.0.26-linux-glibc2.12-x86-64bit /home/oracle/app/myodbc-8.0.26

4）配置ODBC Driver

vim /etc/odbc.ini：

[myodbc8]  
Driver = /home/oracle/app/myodbc-8.0.26/lib/libmyodbc8w.so
Description = Connector/ODBC 8.0 Driver DSN  
SERVER = 192.168.56.110 
PORT = 3307
USER = root
PASSWORD = mysql
DATABASE = oldboy
OPTION = 0  
TRACE = OFF

5）验证ODBC连接

export ODBCINI=/etc/odbc.ini                ---写到.bash_profile中
export LD_LIBRARY_PATH=/home/oracle/app/myodbc-8.0.26/lib:$LD_LIBRARY_PATH
isql myodbc8 -v
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_oldboy                                                |
+-----------------------------------------------------------------+
| t1                                                              |
| t2                                                              |
+-----------------------------------------------------------------+
SQLRowCount returns 2

 6）配置tnsnames.ora

dg4odbc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 15210))
    (CONNECT_DATA =
      (SID = dg4odbc)
      (HS = OK)
    )
  )

7）配置listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 15210))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC15210))
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome)
      (GLOBAL_DBNAME=wmsdb_dg_bj)
   )
   (SID_DESC =
      (SID_NAME = dg4odbc)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-8.0.26/lib:/oracle/app/oracle/product/19c/dbhome/lib)
   )
  )

8）创建init<sid>.ora文件

HS_FDS_CONNECT_INFO=myodbc8           # Data source name in odbc.ini
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL = on
HS_FDS_SUPPORT_STATISTICS=FALSE         
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_NLS_NCHAR=UCS2          
//NVARCHAR/NCHAR和图形数据类型通常以Unicode格式存储数据。unicode字符集因数据库的不同而不同,设置此参数外部数据库保持一致

# ODBC env variables
set ODBCINI=/etc/odbc.ini

9）使上述配置文件生效

$ lsnrctl reload
$ lsnrctl status

10）验证配置是否正确

tnsping myodbc8

11）创建dblink

create public database link myodbc connect to "root" identified by "mysql" using 'dg4odbc'; 
--注意：MySQL用户名和密码，都需要使用双引号，dg4odbc使用单引号。
select count(*) from trans_expert_map@myodbc;

备注：MySQL数据中是区分大小写的，表需要用双引号引起来

 

 

sqldeveloper工具连接mysql参考下面网址

https://blog.csdn.net/wb175208/article/details/85797988