dbsid_mysql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1 )(PORT = 1521))
)
(CONNECT_DATA =
(SID = mysql_test)
)
(HS = OK)
)
测试tnsname连接
[oracle@test admin]$ tnsping dbsid_mysql
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-APR-2018 12:17:58
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = mysql_test)) (HS = OK))
OK (0 msec)
八、dblink创建以及数据访问测试
SQL>create PUBLIC DATABASE LINK dlk connect to "dbtest" identified by "abcd1234" using 'dbsid_mysql';
SQL> select * from "t1"@dlk;
id
----------
10
11
SQL> insert into "t1"@dlk values(30);
1 row created.
九、错误信息以及处理方法
(1)错误01
错误信息:
SQL> select * from t1@dlk;
select * from t1@dlk
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DLK
错误原因以及处理方法:hs/admin/init[sid].ora里配置的HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so不正确,应该是odbc的Lib包
(2)错误02
错误信息:
SQL> select * from "t1"@dlk;
select * from "t1"@dlk
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
错误原因以及处理方法:hs/admin/init[sid].ora里配置的HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确,应该是oracle数据库字符集
(3)错误03:
错误信息:
SQL> select * from t1@dlk;
select * from t1@dlk
*
ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table 'test.T1' doesn't exist
{42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from DLK
错误原因以及处理方法:执行的查询操作,表名需要带双引号,因为mysql默认表名是区分大小写,而oracle是不区分大小写的 select * from "t1"@dlk;