Oracle access MySql via unixODBC by DBLINK(二)
**
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use gtlions
Database changed
mysql> create table access_by_oracle(id int(5),name char(20));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into access_by_oracle values(1,'gtlions');
Query OK, 1 row affected (0.00 sec)
mysql> insert into access_by_oracle values(2,'laidye');
Query OK, 1 row affected (0.00 sec)
mysql> select * from access_by_oracle;
+------+---------+
| id | name |
+------+---------+
| 1 | gtlions |
| 2 | laidye |
+------+---------+
2 rows in set (0.00 sec)
mysql>
好了，MySql已经安装部署完毕并准备可以对外访问了。
1. Oracle访问端配置
我们需要借助unixODBC来访问，如果系统有自带安装好的就可以直接使用，如果没有就要安装它，可以使用yum或者
源码包的方式，由于我这里系统已经安装好了，不再描述，如果需要yum安装它，命令参考如下：
yum -y unixODBC
我本机上已经安装完成的如下包：
[root@gtserdev ~]# rpm -qa|grep unixODBC
unixODBC-devel-2.2.11-10.el5
unixODBC-libs-2.2.11-10.el5
unixODBC-2.2.11-10.el5
unixODBC-kde-2.2.11-10.el5
安装完成后编辑/etc/odbc.ini，内容如下：
[oracle@gtserdev admin]$ cat /etc/odbc.ini
[test]
Driver=/usr/lib/libmyodbc3_r.so
Description=MySQL
Server=192.168.56.101
Port=3306
User=gtlions
Password=000000
Database=gtlions
Option=3
Socket=
上述内容第3行开始为MySql服务器地址、端口、用户、密码、
数据库，酌情修改吧。
然后执行下列命令进行测试，如果能够顺利登入MySQL Client窗口，说明我们的unixODBC功能可以正常：
[root@gtserdev ~]# isql -v test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
我们可以使用select查询下上面我们在MySql当中创建的表access_by_oracle中的数据：
SQL> select * from access_by_oracle;
+-----------+---------------------+
| id | name |
+-----------+---------------------+
| 1 | gtlions |
| 2 | laidye |
+-----------+---------------------+
SQLRowCount returns 2
2 rows fetched
这是我们预期的结果，一切正常。
接下来我们就要配置能够在Oracle当中直接访问MySql的数据了。
首先配置数据访问连接文件：
[oracle@gtserdev ~]$ vi $ORACLE_HOME/hs/admin/inittest.ora
[oracle@gtserdev admin]$ cat $ORACLE_HOME/hs/admin/inittest.ora
# this is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
修改监听文件，添加如下语句：
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /usr/lib:/u01/app/oracle/product/10.2.0/db_1/lib)
)
修改之后结果如下：
[oracle@gtserdev admin]$ cat listener.ora
# listener.ora Network Configuration Fi