建站
咨詢
售后
建站咨詢
新聞中心
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è)公司要多少錢，請(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名稱為：dcrac2,對(duì)方oracle數(shù)據(jù)庫(kù)的用戶名為wlyxpt，密碼--wlyxpt,服務(wù)名為dcrac，由于是rac數(shù)據(jù)庫(kù)該蘭色tnsname配置可由券商相關(guān)dba給出
1.3以crh_snp查詢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-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
Copyright (c) 1997, 2009, Oracle. All rights reserved.
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;
網(wǎng)站欄目：oracle通過(guò)DBlink連接oracle
當(dāng)前網(wǎng)址：http://www.ef60e0e.cn/article/gesooo.html
