oracle建dblink去查询mysql

环境： 源端：Oracle /172.22.32.251/rhel 6.7 目标端:mysql 5.1/172.22.33.122  查询这2个包是否安装 [root@dbtest4 /]# rpm -qa | grep mysql-connector mysql-connector-odbc-5.1.5r1144-7.el6.x86_64 [root@dbtest4 /]#  rpm -qa | grep ODBC unixODBC-devel-2.2.14-14.el6.x86_64 unixODBC-2.2.14-14.el6.x86_64 然后配置odbc配置文件 [root@dbtest4 /]# cat /etc/odbc.ini  [mysqlodbc] Driver       = /usr/lib64/libmyodbc5.so Description  = MySQL ODBC 5.1 Driver DSN SERVER       = 172.22.33.122 PORT         = 3306 USER         = app Password     = app2020 Database     = amro_ho01 OPTION = 0 TRACE =  OFF [root@dbtest4 /]# cat /etc/odbcinst.ini  # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 # Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 测试连接 [root@dbtest4 /]# isql mysqlodbc -v +---------------------------------------+ | Connected!                            | |                                       | | sql-statement                         | | help [tablename]                      | | quit                                  | |                                       | +---------------------------------------+ SQL> show databases +-----------------------------------------------------------------+ | Database                                                        | +-----------------------------------------------------------------+ | information_schema                                              | | amro_ho01                                                       | | amro_ho01_test                                                  | | backup                                                          | | messap_test                                                     | | mysql                                                           | | ogg                                                             | | performance_schema                                              | | qr_code_manage                                                  | | sys                                                             | | xie                                                             | +-----------------------------------------------------------------+ SQLRowCount returns 11 11 rows fetched 配置oracle环境变量 # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/ora11g export ORACLE_SID=amro export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib/$ORACLE_HOME/hs/lib:/usr/lib64:$LD_LIBRARY_PATH: export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" umask 022 ODBCINI=/etc/odbc.ini; export ODBCINI ODBCSYSINI=/etc; export ODBCSYSINI ODBCINSTINI=/etc/odbc.ini export ODBCINSTINI 配置监听,加个静态 [oracle@testdb1 admin]$ cat listener.ora LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = dbtest4)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))     )   ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = mysqlodbc)      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)      (PROGRAM = dg4odbc)      (ENVS=LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib:/usr/lib64)      )   )  然后加个tns omy =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.22.32.251)(PORT = 1521))     (CONNECT_DATA =       (SID = mysqlodbc)     )       (HS = OK)   ) 配置odbc监听 路径：$ORACLE_HOME/hs/admin 注意：名字要跟odbc配置的名字一样 mysqlodbc 配置透明网关。 [oracle@dbtest4 admin]$ vi initmysqlodbc.ora  HS_FDS_CONNECT_INFO = mysqlodbc HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so HS_FDS_SQLLEN_INTERPRETATION=32 HS_LONG_PIECE_TRANSFER_SIZE=1258291 set DBCINI=/etc/odbc.ini 然后测试下连接 [oracle@dbtest4 admin]$ tnsping omy TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 19-OCT-2020 17:04:15 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.22.32.251)(PORT = 1521)) (CONNECT_DATA = (SID = mysqlodbc)) (HS = OK)) OK (0 msec) 创建dblink SQL> create public database link to_mysql connect to "root" identified by "123456" using 'omy'; SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK'; 查询： SQL> select count(*) from "bm_tsk_001"@to_mysql;