成人在线你懂的-成人在线免费小视频-成人在线免费网站-成人在线免费视频观看-日韩精品国产一区二区-日韩精品国产一区

掃一掃
關注微信公眾號

使Oracle能同時訪問多個SQL Server
2007-07-29   天新網 

1、在安裝了Oracle9i Standard Edition或者Oracle9i EntERPrise Edition的Windows機器上(IP:192.168.0.2), 產品要選了透明網關(Oracle Transparent Gateway)里訪問Microsoft SQL Server數據庫。

ORACLE9I_HOME\tg4msql\admin下新寫initpubs.ora和initnorthwind.ora配置文件。

initpubs.ora內容如下:

HS_FDS_CONNECT_INFO="SERVER=SQLSERVER_HOSTNMAE;DATABASE=pubs"

HS_DB_NAME=pubsHS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

initnorthwind.ora內容如下:

HS_FDS_CONNECT_INFO="SERVER=sqlserver_hostname;DATABASE=Northwind"

HS_DB_NAME=Northwind

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

$ORACLE9I_HOME\network\admin 下listener.ora內容如下:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =

192.168.0.2)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = test9)

(ORACLE_HOME = d:\oracle\ora92)

(SID_NAME = test9)

)

(SID_DESC=

(SID_NAME=pubs)

(ORACLE_HOME=d:\Oracle\Ora92)

(PROGRAM=tg4msql)

)

(SID_DESC=

(SID_NAME=northwind)

(ORACLE_HOME=d:\Oracle\Ora92)

(PROGRAM=tg4msql)

)

)

重啟動這臺做gateway的Windows機器上(IP:192.168.0.2)TNSListener服務(凡是按此步驟新增可訪問的SQL Server數據庫時,TNSListener服務都要重啟動)。

2、Oracle8i,Oracle9i的服務器端配置tnsnames.ora, 添加下面的內容:

pubs =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = pubs)

)

(HS = pubs)

)

northwind =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = northwind)

)

(HS = northwind) )

保存tnsnames.ora后,在命令行下:

tnsping pubs

tnsping northwind

出現類似提示,即為成功:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)

(PORT = 1521))) (CONNECT_DATA = (SID = pubs)) (HS = pubs))

OK(20毫秒)

Attempting to contact (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)

(PORT = 1521)))

(CONNECT_DATA = (SID = northwind)) (HS = northwind))OK(20毫秒)

設置數據庫參數global_names=false。

設置global_names=false不要求建立的數據庫鏈接和目的數據庫的全局名稱一致。global_names=true則要求, 多少有些不方便。

oracle9i和oracle8i都可以在DBA用戶下用SQL命令改變global_names參數:

alter system set global_names=false;

建立公有的數據庫鏈接:

create public database link pubs

connect to testuser identified by testuser_pwd using 'pubs';

create public database link northwind

connect to testuser identified by testuser_pwd using 'northwind';

(假設SQL Server下pubs和northwind已有足夠權限的用戶登陸testuser,密碼為testuser_pwd)

訪問SQL Server下數據庫里的數據:

select * from stores@pubs;

...... ......select *

from region@northwind;

...... ......

3、使用時的注意事項

ORACLE通過訪問SQL Server的數據庫鏈接時,用select * 的時候字段名是用雙引號引起來的。例如:

create table stores as select * from stores@pubs;

select zip from stores;

ERROR 位于第 1 行:

ORA-00904: 無效列名

select "zip" from stores;

zip

-----

98056

92789

96745

98014

90019

89076

已選擇6行,用SQL Navigator或Toad看從SQL Server轉移到ORACLE里的表的建表。語句為:

CREATE TABLE stores

("stor_id" CHAR(4) NOT NULL,

"stor_name" VARCHAR2(40),

"stor_address" VARCHAR2(40),

"city" VARCHAR2(20),

"state" CHAR(2),

"zip" CHAR(5))

PCTFREE 10

PCTUSED 40

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 131072

NEXT 131072

PCTINCREASE 0

MINEXTENTS 1

MAXEXTENTS 2147483645

)

/

總結:

Windows下Oracle9i網關服務器在$Oracle9i_HOME\tg4msql\admin目錄下的initsqlserver_databaseid.ora。Windows下Oracle9i網關服務器listener.ora里面:

(SID_DESC=

(SID_NAME=sqlserver_databaseid)

(ORACLE_HOME=d:\Oracle\Ora92)

(PROGRAM=tg4msql)

)

Unix或WINDOWS下ORACLE8I,ORACLE9I服務器tnsnames.ora里面

northwind =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = sqlserver_databaseid)

)

(HS = sqlserver_databaseid)

)

需要sqlserver_databaseid一致才行。

熱詞搜索:

上一篇:面向SQL Server 2005的本機XML Web Services概述(1)
下一篇:如何使用SQL Server 2000中的XML功能

分享到: 收藏
主站蜘蛛池模板: 谢锐韬个人资料| karina hart| 李保田最经典十部电影| 六年级下册语文文言文二则| 美丽的坏女人中文字幕| 夫人电影| 北京 北京 汪峰歌曲| 爱之梦钢琴谱| 男插女b视频| 加藤视频下载| 女忍者椿的心事| 国产伦理女村支书| 狂魔电影| 一类生字和二类生字图| gayvideos| 诺亚方舟电影免费完整版在线观看| 变形记开头结尾优美段落| 超在线视频| 七十二小时| 陈颖芝全部的电影| 幼儿园一日活动的组织与实施| 调教 打屁股 鞭打 拍击视频| 儿子结婚请帖邀请函电子版| 故事电影| 啵乐乐| 女生被打屁股的视频| xxxxxxxxxxxxxxxxxxxxxxxxx| 洪熙官个人资料简介| 失落的星球| 黄视频免费看网站| 斯科| 电视剧瞧这一家子演员表| bbb.| 蝴蝶视频在线观看| 雪中悍刀行第一季电视剧免费观看| 无籍者| 爸爸去哪儿第一季是哪一年播出| 炊事班的故事演员表| a friend in need中文翻译| 小猫叫声吸引猫mp3| 回响电视剧演员表|