数据库通信之ORACLE_CONNECT_MYSQL

1.   先简单介绍下环境

 

Oracle服务器

操作系统：windows7

oracle：10203

mysql服务器

操作系统：linux虚拟机

mysql：5.1

 

2.下载mysql的odbc驱动，我用的是myodbc 5.1并安装

 

3.配置odbc数据源，系统DSN，取名为mysqlora

配置odbc.......

 

4.在E:/oracle/product/10.2.0/db_1/hs/admin下建立initmysqlora.ora文件（类似spfile文件）

输入：

HS_FDS_CONNECT_INFO = mysqlora （ODBC名）
HS_FDS_TRACE_LEVEL = on  

5.在E:/oracle/product/10.2.0/db_1network/admin下修改

listener.ora
添加：
(SID_DESC =
      (SID_NAME = mysqlora)
      (ORACLE_HOME = E:/oracle/product/10.2.0/db_1)
      (PROGRAM = hsodbc)
    )

--oracle10g（重起监听）样本

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME = PLSExtProc)

     (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

     (PROGRAM = extproc)

   )

)
LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

     (ADDRESS = (PROTOCOL = TCP)(HOST = gyh)(PORT = 1521))

   )

)

SID_LIST_LISTENER=

 (SID_LIST =

 (SID_DESC =

  (SID_NAME = hsodbc)

  (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

  (PROGRAM = hsodbc)

 ))

SID_LIST_LISTENER=

 (SID_LIST =

 (SID_DESC =

  (SID_NAME = access)

  (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

  (PROGRAM = hsodbc)

 ))

SID_LIST_LISTENER=

 (SID_LIST =

 (SID_DESC =

  (SID_NAME = mysqlora)

  (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)

  (PROGRAM = hsodbc)

 ))

--oracle9i（重起监听）样本

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

     (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

     )

     (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP)(HOST = vmsvr1)(PORT = 1521))

     )

 ) )
SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME = PLSExtProc)

     (ORACLE_HOME = E:/oracle/ora92)

     (PROGRAM = extproc)

   )

   (SID_DESC =

     (GLOBAL_DBNAME = orcl)

     (ORACLE_HOME = E:/oracle/ora92)

     (SID_NAME = orcl)

   ) )

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME = mysqlora)

     (ORACLE_HOME = E:/oracle/ora92)

     (PROGRAM = hsodbc)

   ))

6.修改

tnsnames.ora
添加：
mysqlora =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = gyh)(PORT = 1521)) --本地机器名
            (CONNECT_DATA =
            (SID=mysqlora)
            )
           (HS =ok)
    )

7.

 创建dblink

create public database link mysqllink connect to "root" identified by "root" using 'mysqlora';

8.访问 mysql库中的表 user

利用dual表测试
select  * fromdual@mysqllink;
over!