怎么做到本地数据库与远程数据库时时相连 怎么做到本地数据库与远程数据库时时相连用什么数据库好?又或者只要每隔一段时间连一次也行,大概用什么方式啊?谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 -- 创建 DBlink就OK啦!-- 在本地创建两个服务名:-- 即修改 tnsnames.ora 文件!-- (此文件一般在:$ORACLE_HOME/network/admin 目录下,-- 如:D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora)-- 类似代码如下:# 我本地的服务名LYMORA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = luoyoumou)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = lymora) ) )# 远程机器的服务名:SZTYORALF7 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.7)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sztyora) ) )# 192.168.5.7 是远程机器的 IP 地址# sztyora 是远程服务器的 Oracle SID# SZTYORALF7 是你创建的服务名!-----------------------------------------------------------------------# 然后在你本地机器的相应用户(你想哪个用户连接远程机器,就是哪个用户)# 下创建一个 DBLink (数据库链接),如:sqlplus /nologconn / as sysdbasys@LYMORA> grant create database link to scott;授权成功。已用时间: 00: 00: 00.07-- 此时创建远程数据库链接有两种方法:-- *1) 不依赖你本机器已经新建的远程服务名 ( sztyoralf7 )scott@LYMORA> create database link sztylf7 2 CONNECT TO hll IDENTIFIED BY szty2009hll 3 using '(DESCRIPTION = 4 (ADDRESS_LIST = 5 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.7)(PORT = 1521)) 6 ) 7 (CONNECT_DATA = 8 (SERVICE_NAME = sztyora) 9 ) 10 )';数据库链接已创建。已用时间: 00: 00: 00.15# *1) sztylf7 是新建的链接名;# *2) hll 是远程机器的一个用户;# *3) szty2009hll 是远程机器 hll 用户的密码;# *4) 192.168.5.7 是远程机器的IP;# *5) 1521 是远程机器的 Oracle 端口号; # *6) sztyora 是远程机器的 ORACLE SID;scott@LYMORA> select count(*) from city@sztylf7; COUNT(*)---------- 397已选择 1 行。已用时间: 00: 00: 05.65-- *2) 依赖你本机器已经新建的远程服务名 ( sztyoralf7 )scott@LYMORA> create database link sztylf7b connect to hll 2 identified by szty2009hll using 'SZTYORALF7';数据库链接已创建。已用时间: 00: 00: 00.00scott@LYMORA> select count(*) from city@sztylf7b; COUNT(*)---------- 397已选择 1 行。已用时间: 00: 00: 04.48-- 以上创建的都是私有链接(没有 public 关键字)-- 若要创建公共链接:scott@LYMORA> create public database link sztylf7b connect to hll 2 identified by szty2009hll using 'SZTYORALF7';-- 此时你本地数据库的其他用户可以用此链接连接到远程数据库!-- 删除链接:drop database link sztylf7b; -- 删除私有链接drop public database link sztylf7b; -- 删除公共链接--------------------------------------------------------------------------------------------------------------------------- DB link使用步骤 --1.设置global_names=false-- alter system set global_names=false --2.创建DB link--- ---------------- 创建私有链接名 -----------------------------------create database link sztylf8CONNECT TO hll IDENTIFIED BY szty2009hll using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.8)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sztyora) ) )';-- 其中 -- *(1) hll 是远程数据库的用户名-- *(2) szty2009hll 是远程数据库的密码-- *(3) 192.168.5.7 是远程数据库的IP(我这里是用的内网IP,你也可以用外网IP,但是那将影响你的查询速度)-- *(4) 1521 是远程数据库启用的端口号-- *(5) sztyora 是远程数据库的SID-- 若要创建公共链接名的话:create public database link sztylf......SELECT * FROM city@sztylf;---------------- 创建公共链接名 -----------------------------------create database link sztylf8 CONNECT TO hll_query IDENTIFIED BY szty2009hll using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.8)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sztyora) ) )';create database link szty245link CONNECT TO scott IDENTIFIED BY bee56915using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sztyora) ) )'; create table xyg_tb2 as select * from xyg_tb2@sztylf8;select forumname from xyg_tb1 where forumname like '%幽默搞笑%';select * from xyg_tb2@sztylf8;CREATE PUBLIC DATABASE LINK testdb CONNECT TO "oracle" IDENTIFIED BY "manager" USING 'MYODBC3';create public database link MYSQLconnect to "oracle" identified by "manager"using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )(CONNECT_DATA = (SID= test) )(HS=OK))';select * from ----------------------------------------------------------------------------------------------------------------5, 编辑Oracle所在计算机的Oracle listener的配置文件,建立一个模拟Oracle Listener的监听方式,为将来建立dblink做准备: vi /ora10g/network/admin/listener.ora 加入如下语句: (SID_DESC = (SID_NAME = test) (ORACLE_HOME = /ora10g) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib) ) listener.ora文件现在的内容变成: SID_LIST_LISTENER = ( SID_LIST = (SID_DESC = (ORACLE_HOME = /ora10g) (PROGRAM = extproc) (GLOBAL_DBNAME=prod) (SID_NAME=prod) ) (SID_DESC =(SID_NAME = test) (ORACLE_HOME = /ora10g) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib) ) ) LISTENER = ( DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) 执行lsnrctl reload使Listener生效: su – oracle lsnrctl reload------6, 编辑Oracle所在计算机中的tnsnames.ora文件,便于建立dblink.注意,此tnsnames的配置可以支持tnsping,但是不能支持sqlplus登录,只用于dblink: vi /ora10g/network/admin/tnsnames.ora test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SID = test) ) (HS = OK)) 7, 在Oracle Database建立dblink: create public database link MYSQL connect to "mysql username" identified by "mysql pwd" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) ) (CONNECT_DATA = (SID= test) ) (HS=OK) )'; 要注意用户名和密码处需要用双引号,否则Oracle所传输的都是大写字母,可能无法登录进入MySQL. 8, 由于MySQL中的表名的大小写敏感,因此需要在进行SQL查询时对表名用双引号扩起来 select * from "tablename"@test本篇文章来源于《点睛考试网》[www.kswchina.com] ;原文链接地址:http://it.kswchina.com/Oracle/zh/506285.html 不小心误删除wmsys.wm_concat函数 查询语句排序问题 按存入数据库的顺序排序 请教一个Oracle中的触发器语句! 请教:Oracle做全外连接的表的个数,是否有限制 怎么将查询结果直接赋给数组呢 Oracle中如何调用存储过程呢?急,谢谢帮忙!!!!!!!!!!!!!!!! Excel 表格怎么导入Oracle数据库和Sql server数据库 关于JAVA ,JSP, 和Oracle 之间的问题 to 数据删除或插入的问题 oracle 查询出数据 计算出比率,想根据比率排序 如何在JDBC中拿到SQL的运行时间 sql排序问题 急 在线等
-- 即修改 tnsnames.ora 文件!
-- (此文件一般在:$ORACLE_HOME/network/admin 目录下,
-- 如:D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora)
-- 类似代码如下:# 我本地的服务名
LYMORA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = luoyoumou)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lymora)
)
)# 远程机器的服务名:
SZTYORALF7 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sztyora)
)
)# 192.168.5.7 是远程机器的 IP 地址
# sztyora 是远程服务器的 Oracle SID
# SZTYORALF7 是你创建的服务名!-----------------------------------------------------------------------
# 然后在你本地机器的相应用户(你想哪个用户连接远程机器,就是哪个用户)
# 下创建一个 DBLink (数据库链接),如:sqlplus /nolog
conn / as sysdba
sys@LYMORA> grant create database link to scott;授权成功。已用时间: 00: 00: 00.07-- 此时创建远程数据库链接有两种方法:-- *1) 不依赖你本机器已经新建的远程服务名 ( sztyoralf7 )
scott@LYMORA> create database link sztylf7
2 CONNECT TO hll IDENTIFIED BY szty2009hll
3 using '(DESCRIPTION =
4 (ADDRESS_LIST =
5 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.7)(PORT = 1521))
6 )
7 (CONNECT_DATA =
8 (SERVICE_NAME = sztyora)
9 )
10 )';数据库链接已创建。已用时间: 00: 00: 00.15# *1) sztylf7 是新建的链接名;
# *2) hll 是远程机器的一个用户;
# *3) szty2009hll 是远程机器 hll 用户的密码;
# *4) 192.168.5.7 是远程机器的IP;
# *5) 1521 是远程机器的 Oracle 端口号;
# *6) sztyora 是远程机器的 ORACLE SID;scott@LYMORA> select count(*) from city@sztylf7; COUNT(*)
----------
397已选择 1 行。已用时间: 00: 00: 05.65-- *2) 依赖你本机器已经新建的远程服务名 ( sztyoralf7 )scott@LYMORA> create database link sztylf7b connect to hll
2 identified by szty2009hll using 'SZTYORALF7';数据库链接已创建。已用时间: 00: 00: 00.00
scott@LYMORA> select count(*) from city@sztylf7b; COUNT(*)
----------
397已选择 1 行。已用时间: 00: 00: 04.48-- 以上创建的都是私有链接(没有 public 关键字)
-- 若要创建公共链接:
scott@LYMORA> create public database link sztylf7b connect to hll
2 identified by szty2009hll using 'SZTYORALF7';-- 此时你本地数据库的其他用户可以用此链接连接到远程数据库!-- 删除链接:
drop database link sztylf7b; -- 删除私有链接
drop public database link sztylf7b; -- 删除公共链接-------------------------------------------------------------------------------------------------------------------------
-- DB link使用步骤
--1.设置global_names=false--
alter system set global_names=false
--2.创建DB link---
---------------- 创建私有链接名 -----------------------------------
create database link sztylf8
CONNECT TO hll IDENTIFIED BY szty2009hll
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sztyora)
)
)';-- 其中
-- *(1) hll 是远程数据库的用户名
-- *(2) szty2009hll 是远程数据库的密码
-- *(3) 192.168.5.7 是远程数据库的IP(我这里是用的内网IP,你也可以用外网IP,但是那将影响你的查询速度)
-- *(4) 1521 是远程数据库启用的端口号
-- *(5) sztyora 是远程数据库的SID-- 若要创建公共链接名的话:
create public database link sztylf
......
SELECT * FROM city@sztylf;---------------- 创建公共链接名 -----------------------------------
create database link sztylf8
CONNECT TO hll_query IDENTIFIED BY szty2009hll
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sztyora)
)
)';
create database link szty245link
CONNECT TO scott IDENTIFIED BY bee56915
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sztyora)
)
)'; create table xyg_tb2 as select * from xyg_tb2@sztylf8;
select forumname from xyg_tb1 where forumname like '%幽默搞笑%';select * from xyg_tb2@sztylf8;CREATE PUBLIC DATABASE LINK testdb
CONNECT TO "oracle" IDENTIFIED BY "manager" USING 'MYODBC3';create public database link MYSQL
connect to "oracle" identified by "manager"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
(CONNECT_DATA = (SID= test) )
(HS=OK)
)';select * from
----------------------------------------------------------------------------------------------------------------5, 编辑Oracle所在计算机的Oracle listener的配置文件,建立一个模拟Oracle Listener的监听方式,为将来建立dblink做准备: vi /ora10g/network/admin/listener.ora 加入如下语句: (SID_DESC = (SID_NAME = test) (ORACLE_HOME = /ora10g) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib) ) listener.ora文件现在的内容变成: SID_LIST_LISTENER = ( SID_LIST = (SID_DESC = (ORACLE_HOME = /ora10g) (PROGRAM = extproc) (GLOBAL_DBNAME=prod) (SID_NAME=prod) ) (SID_DESC =(SID_NAME = test) (ORACLE_HOME = /ora10g) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib) ) ) LISTENER = ( DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) 执行lsnrctl reload使Listener生效: su – oracle lsnrctl reload
------
6, 编辑Oracle所在计算机中的tnsnames.ora文件,便于建立dblink.注意,此tnsnames的配置可以支持tnsping,但是不能支持sqlplus登录,只用于dblink: vi /ora10g/network/admin/tnsnames.ora test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SID = test) ) (HS = OK)) 7, 在Oracle Database建立dblink: create public database link MYSQL connect to "mysql username" identified by "mysql pwd"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
(CONNECT_DATA = (SID= test) )
(HS=OK)
)'; 要注意用户名和密码处需要用双引号,否则Oracle所传输的都是大写字母,可能无法登录进入MySQL. 8, 由于MySQL中的表名的大小写敏感,因此需要在进行SQL查询时对表名用双引号扩起来 select * from "tablename"@test本篇文章来源于《点睛考试网》[www.kswchina.com] ;原文链接地址:http://it.kswchina.com/Oracle/zh/506285.html