需在oracle中访问sql server2000的数据,windows2000,用transparent gateway. oracle9i与sql server 2000在同一台机器上,客户端在另外机器上,具体配置:
listener:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1527))
) )
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mysql)
(program=tg4msql)
(ORACLE_HOME = E:\myoracle)
(SID_NAME = mysql)
)
)透明网关配置:
HS_FDS_CONNECT_INFO="SERVER=cfzqoracle;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
客户端tnsnames 配置:
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1527))
)
(CONNECT_DATA =
(SID = mysql)
(HS=OK)
)
)在sqlserver上有用户对test库操作权限:
create public database link tosql2k connect to USERNAME identified by
PASSWORD using 'tg4msql'
问题:
进行select * from table@tosql2k 时,提示
SQL> select * from t_degree@tos
2 /
select * from t_degree@tosql2k
*
ERROR 位于第 1 行:
ORA-12154: TNS: 无法处理服务名注:tnsping tg4msql 显示ok.
listener:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1527))
) )
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mysql)
(program=tg4msql)
(ORACLE_HOME = E:\myoracle)
(SID_NAME = mysql)
)
)透明网关配置:
HS_FDS_CONNECT_INFO="SERVER=cfzqoracle;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
客户端tnsnames 配置:
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1527))
)
(CONNECT_DATA =
(SID = mysql)
(HS=OK)
)
)在sqlserver上有用户对test库操作权限:
create public database link tosql2k connect to USERNAME identified by
PASSWORD using 'tg4msql'
问题:
进行select * from table@tosql2k 时,提示
SQL> select * from t_degree@tos
2 /
select * from t_degree@tosql2k
*
ERROR 位于第 1 行:
ORA-12154: TNS: 无法处理服务名注:tnsping tg4msql 显示ok.
停止监听
lsnrctl stop
重新启动监听程序
lsnrctl start
USERNAME PASSWORD 'tg4msql'
是 server2000的?不是的话旧错廖.
用它册server2000看看!create public database link tosql2k connect to USERNAME identified by
PASSWORD using 'tg4msql'
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cfzqoracle)(PORT = 1527))
)
(CONNECT_DATA =
(SID = mysql)
) ###########
(HS=OK)
)