oracle database link-阿里云开发者社区

原文整理自网络：

database link基础知识

什么是database link？

database link，它是用来更方便的一个数据库中访问另一个数据库（包括本地和远程的，道理是一样的），一开始，很多人会发生误解，其实是在本地建立的。即数据库连接只是连到别的数据库的快捷方式。

database link是定义一个数据库到另一个数据库的路径的对象，database link允许你查询远程表及执行远程程序。在任何分布式环境里，database都是必要的。另外要注意的是database link是单向的连接。

在创建database link的时候，Oracle在数据字典中保存相关的database link的信息，在使用database link的时候，Oracle通过Oracle Net用用户预先定义好的连接信息访问相应的远程数据库以完成相应的工作。

建立database link之前需要确认的事项：

确认从local database到remote database的网络连接是正常的，tnsping要能成功。
确认在remote database上面有相应的访问权限。

database link分类

database link使用方式分类

类型	Owner	描述
Private	创建database link的user拥有该database link	在本地数据库的特定的schema下建立的database link。只有建立该database link的schema的session能使用这个database link来访问远程的数据库。同时也只有Owner能删除它自己的private database link。
Public	Owner是PUBLIC.	Public的database link是数据库级的，本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此database link来访问相应的远程数据库。
Global	Owner是PUBLIC.	Global的database link是网络级的，When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

database link远程数据库用户验证方法

用户类型	描述	举例说明
Connected user	当创建database link的时候没用用CONNECT TO username指定固定的user的时候当前用户的登陆信息将会被用来验证登陆远程数据库，如果本地用户是SYSTEM的话那么登陆远程数据库的用户也是SYSTEM。注意：Connected user不一定是建立database link的用户，任何一个可以使用该database link的用户都可以是Connected user。	CREATE PUBLIC database link hq USING ‘hq’;
Fixed user	在建立database link的时候如果指定了用户名/密码时，所有使用使用使用该database link的用户，不管是否是建立database link的用户，都将使用指定的用户去登陆远程的数据库。	PUBLIC database link hq CONNECT TO jane IDENTIFIED BY doe USING ‘hq’;
Current user	A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link. Current user links are an aspect of the Oracle Advanced Security option. See Oracle Advanced Security Administrator’s Guide for information about global security	CREATE PUBLIC database link hq CONNECT TO CURRENT_USER using ‘hq’;

database link的使用

创建database link

基本语法

 
  CREATE
   [
  SHARED
  ][
  PUBLIC
  ] 
  database
   
  link
   
  link_name
  
       [
  CONNECT
   
  TO
   
  user
   
  IDENTIFIED
   
  BY
   
  password
  ]
       [
  AUTHENTICATED
   
  BY
   
  user
   
  IDENTIFIED
   
  BY
   
  password
  ]
       [
  USING
   
  '
  connect_string
  '
  ] 
 

创建database link选项说明

database link连接方式

取值	说明
不指定	默认值建立一个dedicated的连接，每一个使用database link的本地session都会对应有一个远程数据库的session。
SHARED	创建一个共享的数据库连接，同时要指定database link_authentication。使用shared方式的database link是数据库会限制到远程数据库的连接的数量，这样以避免过多的连接对远程数据库造成太大的压力。在使用shared database link的时候，到database link的连接会在连接以后与本地连接断开，为防止未授权的session使用此链接而要求在创建shared database link的时候必须要指定database link_authentication。 (from Oracle document: A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.)

取值

说明

不指定

默认值建立一个dedicated的连接，每一个使用database link的本地session都会对应有一个远程数据库的session。

SHARED

创建一个共享的数据库连接，同时要指定database link_authentication。
使用shared方式的database link是数据库会限制到远程数据库的连接的数量，这样以避免过多的连接对远程数据库造成太大的压力。在使用shared database link的时候，到database link的连接会在连接以后与本地连接断开，为防止未授权的session使用此链接而要求在创建shared database link的时候必须要指定database link_authentication。
(from Oracle document: A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.)

共享链接更多资料共享数据库链接是指该链接的多个用户可以共享同一个底层网络连接。例如，在有四位用户的MTS（多线程服务器）环境下，每一个共享服务器进程都将与远程服务器有一个物理链接，这四位用户共享这两个链接。
表面上，共享链接乍一听起来像是一件好事。在某些环境下的确如此，但是，当你考虑使用共享链接时，应当意识到这有许多局限性和警告：
如果你使用一个专用的服务器连接来连接到你的本地数据库，链接只能在你从那些连接中创建的多重会话间共享。在MTS环境里，每一个共享服务器进程潜在地打开一个链接。所有的会话被同一共享服务器进程提供并且分享被那个进程打开的任意共享链接。因为在MTS环境里的一个共享服务器进程能够服务于许多用户连接，共享链接的使用可能导致打开的链接远多于所必须的链接。用SHARED关键字建立共享数据库链接。还必须使用AUTHENTICATED BY 子句在远程系统上指定一有效的用户名和口令。如下命令建立一个共享的、公用的、连接用户数据库链接：
CREATE SHARED PUBLIC database link GNIS
AUTHENTICATED BY DUMMY_USER IDENTIFIED BY SECRET
USING ‘GNIS’;
要获得创建链接和管理分布式系统的更多资料，请查阅Oracle Technology Network ( http://otn.oracle.com/)。
使用AUTHENTICATED BY子句稍微有些困扰，但是由于实现共享链接的方式安全性决定它是必须的。这个例子中的用户名和口令DUMMY_USER/SECRET必须在远程系统上有效。然而，远程系统上使用的帐户仍就是连接用户的帐户。如果我以JEFF/SECRET登陆到我的本地数据库并使用我刚建好的共享链接，将会发生以下一系列事件：
为了打开链接，Oracle使用DUMMY_USER/SECRET向远程数据库授权。然后，Oracle试图使用HMAD/SECRET使我登陆到远程数据库。共享链接的主要目的是减少两个数据库服务器之间的底层网络连接数量。它们最适合于MTS环境，在那你拥有大量的通过这一链接访问远程数据库的用户。观念上，你想让用户数量超过共享服务器进程的数量。那么你可以通过为每一共享服务器进程打开一个链接而不是每位用户打开一个链接的方法，节省资源。 database link使用方式（见上）

取值	说明
不指定	默认值建立一个private的database link
PUBLIC	公共连接，这样的连接可以被数据的所有的用户访问

database link用户验证方法（见上）

取值	说明
不指定	默认值采取Connected User的验证方法
CONNECT TO CURRENT_USER	采取CURRENT_USER的验证方式
CONNECT TO user_name IDENTIFIED BY password	采取Fiexed User的验证方式

database link创建举例

SQL Statement	Connects To Database	Connects As	Link Type
CREATE database link sales.us.americas.acme_auto.com USING ‘sales_us’;	sales using net service name sales_us	Connected user	Private connected user
CREATE database link foo CONNECT TO CURRENT_USER USING ‘am_sls’;	sales using service name am_sls	Current global user	Private current user
CREATE database link sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger USING ‘sales_us’;	sales using net service name sales_us	scott using password tiger	Private fixed user
CREATE PUBLIC database link sales CONNECT TO scott IDENTIFIED BY tiger USING ‘rev’;	sales using net service name rev	scott using password tiger	Public fixed user
CREATE SHARED PUBLIC database link sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY anupam IDENTIFIED BY bhide USING ‘sales’;	sales using net service name sales	scott using password tiger, authenticated as anupam using password bhide	Shared public fixed user

database link的创建操作：

第一种创建办法：

例句解析：create database link User2 connect to scott identified by liu123 using 'ORCL '

create database link USER2（连接名字，利用该名字就可以直接连上数据库） connect to SCOTT1（连接远程数据库时的用户名） identified by liu123（密码） using 'ORCL '（服务名称，即在本地的net manager中配置的连接远程数据库的服务名,也就是当前客户端数据库中TNSNAMES.ORA文件里定义的别名名称.可以用NET CONFIG或者直接修改TNSNAMES.ORA里定义.）

第二种创建办法：

直接建立链接

CREATE database link link_name
CONNECT TO user IDENTIFIED BY screct
USING '(DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = sales.company.com)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = sales)
   )
)';

注意：别忘了，设置参数文件中的globe_name=false，修改后重启。

假如创建全局dblink，则必须使用systm或sys用户，在database前加public。

使用database link查询数据：

 
  -- 最简单的用法
  
  SELECT
   * 
  FROM
   
  table_name
  @
  database
   
  link
  ;
  
  -- 不想让使用的人知道database link的名字的时候
  
  -- 建一个别名包装一下
  
  CREATE
   
  SYNONYM
   
  table_name
   
  FOR
   
  table_name
  @
  database
   
  link
  ;

  SELECT
   * 
  FROM
   
  table_name
  ;

  -- 或者，也可以建立一个视图来封装
  
  CREATE
   
  VIEW
   
  table_name
   
  AS
   
  SELECT
   * 
  FROM
    
  table_name
  @
  database
   
  link
  ;

database link删除

 
  -- 先查询已经建立的database link  
 
  SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';
  
  -- 删除public类型的database link
  
  DROP PUBLIC 
  database
   
  link
   
  link_name
  ;
  
  -- 删除非public类型的database link
  
  -- 注意：只有owner自己能删除自己的非public类型database link
  
  DROP
   
  database
   
  link
   
  link_name
  ;

查看database link的信息

查看系统database link的基本信息

DBA_DB_LINKS (ALL_DB_LINKS/USER_DB_LINKS): DBA_DB_LINKS视图为每一定义的链接返回一行。OWNER 列和DB_LINK列分别显示了这一链接的所有者及名称。对公用数据库链接，OWNER列将包含’PUBLIC’。如果你建立固定用户链接，用户名应在DBA_DB_LINKS视图的USERNAME列里。ALL_DB_LINKS 视图和 USER_DB_LINKS视图与 DBA_DB_LINKS视图相类似-它们分别显示了你能够访问的所有链接及你所拥有的全部链接。

 
  COL
   
  OWNER
   
  FOR
   
  A15
  
  COL
   
  DB_LINK
   
  FOR
   
  A25
  
  COL
   
  HOST
   
  FOR
   
  A25
  
  COL
   
  USERNAME
   
  FOR
   
  A15
  
  SELECT * FROM DBA_DB_LINKS;

DBA_OBJECTS (ALL_OBJECTS/USER_OBJECTS): 在这个视图里面只能查询到系统有那些database link以及他们的owner，创建时间等信息。

 
  COL
   
  OWNER
   
  FOR
   
  A15
  
  COL
   
  OBJECT_NAME
   
  FOR
   
  A25
  
  COL
   
  OBJECT_TYPE
   
  FOR
   
  A25
  
  SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHEREOBJECT_TYPE LIKE 'database link';

查看保存的Fixed user database link所保存的用户密码

这是比较危险的一件事，有必要对表sys.link$的权限进行限制。

 
  col
   
  host
   
  for
   
  a15
  
  col
   
  userid
   
  for
   
  a15
  
  col
   
  passwordx
   
  for
   
  a40
  
  col
   
  name
   
  for
   
  a20
  
  select name, host, userid, passwordx from sys.link$;

看看当前有什么人在用database link

View	Purpose
V$database link	Lists all open database links in your session, that is, all database links with the IN_TRANSACTION column set to YES.
GV$database link	Lists all open database links in your session along with their corresponding instances. This view is useful in an Oracle Real Application Clusters configuration.

 
  COL
   
  DB_LINK
   
  FORMAT
   
  A25
  

  COL
   
  OWNER_ID
   
  FORMAT
   
  99999
   
  HEADING
   &
  quot
  ;
  OWNID
  &
  quot
  ;

  COL
   
  LOGGED_ON
   
  FORMAT
   
  A5
   
  HEADING
   &
  quot
  ;
  LOGON
  &
  quot
  ;

  COL
   
  HETEROGENEOUS
   
  FORMAT
   
  A5
   
  HEADING
   &
  quot
  ;
  HETER
  &
  quot
  ;

  COL
   
  PROTOCOL
   
  FORMAT
   
  A8
  

  COL
   
  OPEN_CURSORS
   
  FORMAT
   
  999
   
  HEADING
   &
  quot
  ;
  OPN_CUR
  &
  quot
  ;

  COL
   
  IN_TRANSACTION
   
  FORMAT
   
  A3
   
  HEADING
   &
  quot
  ;
  TXN
  &
  quot
  ;

  COL
   
  UPDATE_SENT
   
  FORMAT
   
  A6
   
  HEADING
   &
  quot
  ;
  UPDATE
  &
  quot
  ;

  COL
   
  COMMIT_POINT_STRENGTH
   
  FORMAT
   
  99999
   
  HEADING
   &
  quot
  ;
  C_P_S
  &
  quot
  ;

  SELECT
   * 
  FROM
   
  V
  $
  database
   
  link
  
 / 
 

database link tips and scripts

通过database link去SELECT远程数据库的一个表也是一个事务吗？

 
  select
   * 
  from
   
  v
  $
  transaction
  ;
  
  -- 没有记录，说明没有事务
  
  
  -- 通过database link连接远程数据库，select 其中一个表
  
  
  select
   * 
  from
   
  bbs_news
  @
  mylink
  
  
  select
   * 
  from
   
  v
  $
  transaction
  ;
  
  -- 发现有一条记录。 
 

解释

因为本地数据库只是将对应的sql发送给远程数据库执行，接受remote db返回的结果，但他并不知道是否该sql修改了数据；所以需要为select 语句也标示一个事务。

具体可以参考otn 分布式数据库手册，所以在用database link远程访问时,要加上set transaction read only; (from www.itpub.net)

close database link

 
  ALTER SESSION CLOSE database link sales; 
 

database link的字符集问题

见参考资料（暂时未测试，使用后再完善这一部分）

下一步计划 => Oracle分布式事务处理

参考资料

Oracle document NO. B14231 – Administrator’s Guide
Metalink Note 270774.1 – Character Sets & Conversion – Frequently Asked Questions
database link的使用
http://sunrise-king.javaeye.com/blog/267852
Using Oracle Global database links
http://www.oreillynet.com/pub/a/oreilly/oracle/news/oraclenet8_1200.html
http://www.itpub.net/thread-371255-1-1.html
使用database link,两个ORACLE数据库字符集不一样?
http://www.itpub.net/viewthread.php?tid=613581
dblink及字符集转换问题。（UTF8->WE8ISO8859P1)
http://kennyblues.itpub.net/post/31846/455855

oracle database link