oracle通過(guò)DBlink連接oracle

1.1賦權(quán)（將crh_snp用戶賦權(quán)具體創(chuàng)建dblink權(quán)限）　

新鄭網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)公司,新鄭網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為新鄭上千余家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)公司要多少錢(qián)，請(qǐng)找那個(gè)售后服務(wù)好的新鄭做網(wǎng)站的公司定做！

在sys用戶下，把CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK權(quán)限授予給你的用戶

grant CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASE LINK to crh_snp;

1.2具體創(chuàng)建db_link

然后以crh_snp用戶登錄本地?cái)?shù)據(jù)庫(kù),執(zhí)行以下語(yǔ)名

create database linkdcrac2

connect to wlyxptidentified by wlyxpt

using '(DESCRIPTION =

(ADDRESS_LIST =

(address = (protocol = tcp)(host = 192.19.0.81)(port = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dcrac)

(INSTANCE_NAME = dcrac2)

(failover_mode =

(backup = dcrac1)

(type = select)

(method = preconnect)

)

)';

---說(shuō)明：本次創(chuàng)建的db_link名稱(chēng)為：dcrac2,對(duì)方oracle數(shù)據(jù)庫(kù)的用戶名為wlyxpt，密碼--wlyxpt,服務(wù)名為dcrac，由于是rac數(shù)據(jù)庫(kù)該蘭色tnsname配置可由券商相關(guān)dba給出

1.3以crh_snp查詢(xún)ods_ufs.his_his_delive表數(shù)據(jù)（obs所屬表，wlyxpt用戶有相應(yīng)權(quán)限）

select * fromods_ufs.his_his_deliver@dcrac2;

2、oracle通過(guò)DBlink連接MySQL

oracle：系統(tǒng)redhat 5.5 數(shù)據(jù)庫(kù)：11.2.0.1

mysql：系統(tǒng)centos6.3 數(shù)據(jù)庫(kù)：5.6.21

2.1先裝 mysql-connector-odbc和unixODBC

[root@rac1 ~]# rpm -qa | grep mysql

mysql-5.0.77-4.el5_4.2

mysql-connector-odbc-5.1.13-1.rhel5

[root@rac1 ~]# rpm -qa | grep ODBC

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

unixODBC-devel-2.2.11-7.1

unixODBC-2.2.11-7.1

2.2.配置 /etc/odbc.ini

[myodbc3]

Driver = /usr/lib64/libmyodbc5.so

Description = MySQL ODBC 5.1 Driver DSN

SERVER = 192.1.1.200

PORT = 3306

USER = bi

Password = 123456

Database = chanpin

OPTION = 3

SOCKET =

charset = utf8

2.3.配置/etc/odbcinst.ini

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

FileUsage = 1

2.4.測(cè)試連接

[root@rac1 ~]# isql myodbc3 -v

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

2.5.配置oracle環(huán)境變量

export ORACLE_BASE=/u01/app/oracle

export GRID_HOME=/u01/grid

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=rac1

export BASE_PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$BASE_PATH:/usr/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

ODBCINI=/etc/odbc.ini; export ODBCINI

ODBCSYSINI=/etc; export ODBCSYSINI

ODBCINSTINI=/etc/odbc.ini

export ODBCINSTINI

2.6.配置監(jiān)聽(tīng)

listener.ora文件:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(PROGRAM = dg4odbc)

(SID_NAME= myodbc3)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib:/usr/lib64)

)

tnsname.ora文件:

myodbc3=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))

(CONNECT_DATA =

(SID = myodbc3))

(HS = OK)

)

2.7.配置odbc監(jiān)聽(tīng)

路徑：$ORACLE_HOME/hs/admin

注意：名字要跟odbc配置的名字一樣我這里是myodbc3

[oracle@rac1 admin]$cd $ORACLE_HOME/hs/admin

[oracle@rac1 admin]$ cat initmyodbc3.ora

HS_FDS_CONNECT_INFO = myodbc3

HS_FDS_TRACE_LEVEL = ON

HS_FDS_TRACE_FILE_NAME = odbc_test.log

HS_FDS_TRACE_LEVEL = 4

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_FDS_SUPPORT_STATISCTICS = FALSE

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1

#HS_FDS_SQLLEN_INTERPRETATION=32

set ODBCINI = /etc/odbc.ini

2.8.測(cè)試監(jiān)聽(tīng)

[oracle@rac1 admin]$ tnsping myodbc3

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-NOV-2014 03:02:16

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))

OK (0 msec)

2.9.創(chuàng)建dblink

SQL>create public database link myodbc connect to "bi" identified by "123456" using'myodbc3';

SQL>select count(*) from "t_user"@myodbc;

COUNT(*)

----------

53980

3、刪除dblink

DROPPUBLICDATABASELINK tomysql;

新聞名稱(chēng)：oracle通過(guò)DBlink連接oracle
文章網(wǎng)址：http://muchs.cn/article0/gesooo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、網(wǎng)站策劃、網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、用戶體驗(yàn)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容