oracle dblink

oracle dblink 是干嘛的：比如现在有俩个数据库，都是单独的，如果我们现在登陆当前数据库，想访问另一个数据库，

如果我们建了DBLINK我们就可以直接在当前数据库取访问另一个数据库。 如select * from user@xe 这样我们就可以直接访问另一个数据库的user表

感谢oracle群cryking的qq远程指导。

测试环境是windows xp +oracle 11g，本机两个oracle实例ncbeta 和sen

C:\Documents and Settings\dell>services.msc --sql下可以host services.msc

SYS@sen>create database link ncbeta11 connect to scott identified by tiger using 'NCBETA';

数据库链接已创建。

格式:

create public database link link_name connectto user identified by password using 'SID';

　　则（在实例sen）创建了一个连接到10.130.38.19 ccpbs上（ncbeta）以ecbm_userName 用户的连接

查询数据时带上@ecbm_19 就ok了 如select * from emp@ecbm_19;

删除 DROP PUBLIC DATABASE LINK ecbm_19;

查询globalname

SYS@sen>select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
SEN

 

这个时候监听要正常--我这里netca重新配置了一下才行

SYS@sen>conn scott/tiger@ncbeta
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

 

就可以使用dblink在sen下查询ncbeta的数据

SYS@sen>select * from emp@ncbeta11;

3.11号更新：感谢QQ好友涅的远程

从宿舍带来了自己的笔记本，做一个远程的dblink，笔记本和办公室的台式机都是通过网线连接到同一个tplink路由器上（刚开始笔记本通过路由器的无线连接做的监听不行）.

这里笔记本的oracle作为远程数据库服务器（sid是jsce），办公室台式机作为本机来连接它（sid是ncbea）

笔记本通过了网线和无线都联网了，网线的ip是192.1681.116，无线的是192.168.1.115

E:\>ipconfig

Windows IP Configuration

Ethernet adapter 本地连接:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.1.116
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

Ethernet adapter 无线网络连接:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.1.115
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

之后修改listener和tnsnames文件（后来发现修改host为192.168.1.116无效，怎么也tsnping自己ping不通，重新netca建立）

建立之后的tnsnames如下：

JSCE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = jsce)
)
)

listener如下

# listener.ora Network Configuration File: E:\app\xs\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\app\xs\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\xs\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = localhost:jsce)
(SID_NAME = jsce)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\app\xs\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\xs\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jsce-1f0fab716a)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = E:\app\xs

测试可以tsnping自己通过

E:\>tnsping 192.168.1.115

在台式机本机上创建dblink：

SYS@ncbeta>CREATE PUBLIC DATABASE LINK db1
  2  　　CONNECT TO ncv5 IDENTIFIED BY nc502
  3  　　USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT =
 1521)))(CONNECT_DATA =(SERVICE_NAME = jsce)))'
  4  /

这里的connect to 用户和密码ncv5 nc502是远程机jsce下的（这里host写成了192.168.1.115,116应该也行）

确定能够tnsping通远程机

C:\Documents and Settings\dell>tnsping 192.168.1.115

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-3月 -2013 16:45:12

Copyright (c) 1997, 2010, Oracle. All rights reserved.

已使用的参数文件:
C:\app\dell\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

已使用 HOSTNAME 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.115)(PORT
=1521)))
OK (50 毫秒)

tips：如果ping不同，会有如下报错：

之后就可以使用dblink，在本机查询远程机的数据库

比如，我特意修改了远程机上一个工程项目名称为‘夏森...’

SYS@ncbeta>select jobname from bd_jobbasfil@db1 where jobname like '夏森%';

JOBNAME
--------------------------------------------------------------------------------
夏森的dblink

update的操作，基本解决真东方之子的问题：

在台式机上面通过dblink关联远程oracle作为条件来更新：

SYS@ncbeta>update ncv5.bd_jobbasfil set jobname=(select jobname from bd_jobbasfil@db1 where jobname
like '夏森%') where pk_jobbasfil=(select pk_jobbasfil from bd_jobbasfil@db1 where jobname like '夏森
%');

已更新 1 行。

SYS@ncbeta>commit;

提交完成。

查询

通过 all_db_links数据字典来查询;

18:20:58 更新

cryking的merge用法，解决上面sql复杂：

merge into ncv5.bd_jobbasfil s
using (select pk_jobbasfil, jobname
         from bd_jobbasfil@db1
        where jobname like '夏森%') t
on s.pk_jobbasfil = t.pk_jobbasfil
when matched then
  update set s.jobname = t.jobname

不过提示缺少on关键字

加上括号就行了

merge into ncv5.bd_jobbasfil s
using (select pk_jobbasfil, jobname
         from bd_jobbasfil@db1
        where jobname like '夏森%') t
on  (s.pk_jobbasfil= t.pk_jobbasfil)
when matched then
  update set  s.jobname = t.jobname

这个强，第一次使用merge

本文转自东方之子736651CTO博客，原文链接： http://blog.51cto.com/ecloud/1194395，如需转载请自行联系原作者