假設我們要在ORACLE裡同時能訪問SQL Server裡默認的pubs和Northwind兩個數據庫
產品要選了透明網關(Oracle Transparent Gateway)裡要訪問Microsoft SQL Server數據庫
$ORACLE
initpubs
HS_FDS_CONNECT_INFO=
HS_DB_NAME=pubs
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
initnorthwind
HS_FDS_CONNECT_INFO=
HS_DB_NAME=Northwind
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
(藍色字的部分可以根據具體要訪問的SQL Server數據庫的情況而修改)
$ORACLE
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test
(ORACLE_HOME = d:\oracle\ora
(SID_NAME = test
)
(SID_DESC=
(SID_NAME=pubs)
(ORACLE_HOME=d:\Oracle\Ora
(PROGRAM=tg
)
(SID_DESC=
(SID_NAME=northwind)
(ORACLE_HOME=d:\Oracle\Ora
(PROGRAM=tg
)
)
重啟動這台做gateway的windows機器上(IP:
(凡是按此步驟新增可訪問的SQL Server數據庫時
pubs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)
(CONNECT_DATA =
(SID = pubs)
)
(HS = pubs)
)
northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)
(CONNECT_DATA =
(SID = northwind)
)
(HS = northwind)
)
保存tnsnames
tnsping pubs
tnsping northwind
出現類似提示
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
(PORT =
OK(
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
(PORT =
OK(
設置數據庫參數global_names=false
設置global_names=false不要求建立的數據庫鏈接和目的數據庫的全局名稱一致
global_names=true則要求
oracle
alter system set global_names=false;
建立公有的數據庫鏈接:
create public database link pubs connect to testuser identified by testuser_pwd using
create public database link northwind connect to testuser identified by testuser_pwd using
(假設SQL Server下pubs和northwind已有足夠權限的用戶登錄testuser
訪問SQL Server下數據庫裡的數據:
select * from stores@pubs;
select * from region@northwind;
ORACLE通過訪問SQL Server的數據庫鏈接時
例如
create table stores as select * from stores@pubs;
select zip from stores
ERROR 位於第
ORA
select
zip
已選擇
用SQL Navigator或Toad看從SQL Server轉移到ORACLE裡的表的建表語句為:
CREATE TABLE stores
(
PCTFREE
PCTUSED
INITRANS
MAXTRANS
TABLESPACE users
STORAGE (
INITIAL
NEXT
PCTINCREASE
MINEXTENTS
MAXEXTENTS
)
/
總結: WINDOWS下ORACLE
WINDOWS下ORACLE
(SID_DESC=
(SID_NAME=sqlserver_databaseid)
(ORACLE_HOME=d:\Oracle\Ora
(PROGRAM=tg
)
UNIX或WINDOWS下ORACLE
northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
)
(CONNECT_DATA =
(SID = sqlserver_databaseid)
)
(HS = sqlserver_databaseid)
)
sqlserver_databaseid一致才行
From:http://tw.wingwit.com/Article/program/Oracle/201311/17434.html