在oracle里直接訪問sql server 的設定方法

一天在網上閒逛,看見一片不錯的文章講的是oracle 透明網關的配置問題即:oracle里直接訪問sql server ,所以就做了一下測試.具體在內文[@more@]1. 環境:OS Windwos 2000 server ,ORACLE 9.2.0.1 DBname MTH 及安裝ORACLE 的電腦名稱MTH01 ,SQL Server2000 及安裝SQL Server 的電腦名稱 STH01 2. 象一般情況一樣安裝SQL Server ,安裝oracle的時候添加Oracle Transparent Gateway選項,安裝完oracle後可以看到tg4msql文件夾表示安裝沒有問題. 3. 配置D:oracleora92tg4msqladmin下initpubs.ora,initnorthwind.ora(直接修改初始的initsample.ora就可以了),內容為: # HS init parameters # HS_FDS_CONNECT_INFO="SERVER=STH01;DATABASE=PUBS" HS_DB_NAME=PUBS HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER # HS init parameters # HS_FDS_CONNECT_INFO="SERVER=STH01;DATABASE=Northwind" HS_DB_NAME=Northwind HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER 4. 修改D:oracleora92networkadmin下的listener.ora文件修改完後restart一下. LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MTH01)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracleora92) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = Pubs) (ORACLE_HOME = d:oracleora92) (PROGRAM = tg4msql) ) (SID_DESC = (SID_NAME = Northwind) (ORACLE_HOME = d:oracleora92) (PROGRAM = tg4msql) ) ) 5. 修改D:oracleora92networkadmin下的tnsnames.ora文件 PUBS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MTH01)(PORT = 1521)) ) (CONNECT_DATA = (SID = pubs) ) (HS = pubs) ) NORTHWIND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MTH01)(PORT = 1521)) ) (CONNECT_DATA = (SID = Northwind) ) (HS = Northwind) ) MTH= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = MTH01)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = MTH) ) ) 然後 tnsping pubs 使用 TNSNAMES 介面程式來解析別名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = MTH01)(PORT = 1521))) (CONNECT_DATA = (SID = pubs)) (HS = pubs)) OK (30 msec) Northwind應該可以得到同樣結果 6. 設置數據庫參數global_names=false。 設置global_names=false不要求建立的數據庫鏈結和目的數據庫的全局名稱一致。 global_names=true則要求, 多少有些不方便。 oracle9i和oracle8i都可以在DBA用戶下用SQL命令改變global_names參數 7. 在SQL Server 上建立一個有比較大權限的user GST Password test 8. create public database link pubs connect to GST identified by test using pubs; northwind同樣的方法create 9.測試設定是否OK select * from jobs@pubs; job_id job_desc min_lvl max_lvl 1 1 New Hire - Job not specified 10 10 2 2 Chief Executive Officer 200 250 3 3 Business Operations Manager 175 225 4 4 Chief Financial Officier 175 250 5 5 Publisher 150 250 6 6 Managing Editor 140 225 7 7 Marketing Manager 120 200 8 8 Public Relations Manager 100 175 9 9 Acquisitions Manager 75 175 10 10 Productions Manager 75 165 11 11 Operations Manager 75 150 12 12 Editor 25 100 13 13 Sales Representative 25 100 14 14 Designer 25 100 Northwind同樣的方法 10. 參考文件上有的提醒,貼出來,肯定用的照: ORACLE通過訪問SQL Server的數據庫鏈結時，用select * 的時候字段名是用雙引號引起來的。 EX: create table stores as select * from stores@pubs; select zip from stores；ERROR 位於第 1 行:ORA-00904: 無效列名 select "zip" from stores; zip----- 980569278996745980149001989076 耶!成功了!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/202861/viewspace-785098/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-785098/