oracle dblink 解析

各个数据库版本间dblink情况

Server Version
Client  11.1.0 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 8.1.6 8.1.5 8.0.6 8.0.5 7.3.4
11.1.0 Yes Yes #6 Yes #6 ES #5 No No No #3 No #3 No #3 No #3 No #3
10.2.0 Yes #6 Yes Yes ES #5 No Was No #3 No #3 No #3 No #3 No #3
10.1.0(#4) Yes #6 Yes Yes ES Was Was #2 No #3 No #3 No #3 No #3 No #3
9.2.0 ES #5 ES #5 ES ES Was Was No No Was No No #1
9.0.1 No No Was Was Was Was Was No Was No Was
8.1.7 No Was Was Was Was Was Was Was Was Was Was
8.1.6 No No No No Was Was Was Was Was Was Was
8.1.5 No No No No No Was Was Was Was Was Was
8.0.6 No No No Was Was Was Was Was Was Was Was
8.0.5 No No No No No Was Was Was Was Was Was
7.3.4 No No No Was Was Was Was Was Was Was Was

 

物理上存放于网络的多个ORACLE数据库，逻辑上可以看成一个单一的大型数据库，用户可以通过网络对异地数据库中的数据进行存取，而服务器之间的协同处理对于工作站用户及应用程序而言是完全透明的，开发人员无需关心网络的链接细节、数据在网络节点中的具体分布情况和服务器间的协调工作过程。

数据库之间的链接建立在DATABASE LINK上。要创建一个DATABASE LINK，必须首先在建立链接的数据库上设置链接字符串，即配置一个远程数据库的本地网络服务名。

链接字符串的配置参见《客户端连接服务器》一节。

数据库全局名称可以用以下命令查出：

SELECT * FROM GLOBAL_NAME;

修改可以用以下语句来修改参数值：

ALTER SYSTEM SET GLOBAL_NAME=TRUE/FALSE;

<1>、当数据库参数global_name=false时，就不要求数据库链接名称跟远端数据库名称一样。

oracle数据库之间进行连接通讯。
创建数据库链接的语法如下：
CREATE [PUBLIC] DATABASE LINK link

CONNECT TO username IDENTIFIED BY password

USING ‘connectstring’

其中：
--demona为用net8 easy config创建的连接字符串
目的方数据库的init.ora文件中的global_names设为false
重新启动数据库设置才能生效
或者用sys用户执行

注意：创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限，用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK权限在DBA中）。

一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的。

创建数据库链接时，还可以使用缺省登录方式，即不指定远程数据库的用户名和密码：

create public database link zrhs_link

using ‘zrhs’;

在不指定用户名和口令的情况下，ORACLE使用当前的用户名和口令登录到远程数据库。

USING后面指定的是链接字符串，也就是远程数据库的网络服务名，这个服务名保存在TNSNAMES.ORA文件中，在该文件中定义了协议、主机名、端口和数据库名。

删除数据库链接的语句是：

DROP [PUBLIC] DATABASE LINK zrhs_link

数据库链接的引用

一般情况下引用数据库链接，可以直接将其放到调用的表名或视图名称后面，中间使用一个 @ 作为分割符：

SELECT * FROM worker@zrhs_link;

对于经常使用的数据库链接，可以建立一个本地的同义词，方便使用：

CREATE SYNONYM worker_syn FOR worker@zrhs_link;

还可以建立一个本地的远程视图，方便使用：

CREATE VIEW worker AS SELECT * FROM worker@zrhs_link where… ;

现在本视图可与本地数据库中的任何其它视图一样对待，也可以授权给其它用户，访问此视图，但该用户必须有访问数据库链接的权限。

对于另外一种情况，所要访问的表不在数据库链接中指定的远程帐户下，但该帐户有访问该表的权限，那么我们在表名前要加上该表的用户名：

SELECT * FROM camel.worker@zrhs_link ;

<2>、当数据库参数global_name=true时，那就要求数据库链接名称跟远端数据库名称一样
数据库全局名称可以用以下命令查出

SELECT * FROM GLOBAL_NAME;

atabase link(dblink)--数据库链路
创建dblink的语法：
sql>create database link 数据库链路名
connect to 用户名 identified by 口令
using '主机字符串名';
如：
sql>create database link ora9i.us.oracle.com ### 这里的us.oracle.com为oracle默认域名 ###
connect to scott identified by tiger
using 'sun';

1)dblink名必须与远程数据库的全局数据库名（global_name）相同；
2)用户名，口令为远程数据库用户名，口令;
3)主机字符串为本机tnsnames.ora中定义的串;
4)两个同名的数据库间不得建立dblink;
然后，你就可以通过dblink访问远程数据库了。
如：
sql>select * from 表名@ora9i.us.oracle.com;
还可以建立快照(snapshot)通过dblink实现远程数据自动传输。

查看所有的数据库链接，进入系统管理员SQL>操作符下，运行命令：

SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';

===============================================================

易混淆术语介绍：
Db_name：对一个数据库（Oracle database）的唯一标识，该数据库为第一章讲到的Oracle database。这种表示对于单个数据库是足够的，但是随着由多个数据库构成的分布式数据库的普及，这种命令数据库的方法给数据库的管理造成一定的负担，因为各个数据库的名字可能一样，造成管理上的混乱。为了解决这种情况，引入了Db_domain参数，这样在数据库的标识是由Db_name和Db_domain两个参数共同决定的，避免了因为数据库重名而造成管理上的混乱。这类似于互连网上的机器名的管理。我们将Db_name和Db_domain两个参数用’.’连接起来，表示一个数据库，并将该数据库的名称称为Global_name，即它扩展了Db_name。Db_name参数只能由字母、数字、’_’、’#’、’$’组成，而且最多8个字符。

Db_domain：定义一个数据库所在的域，该域的命名同互联网的’域’没有任何关系，只是数据库管理员为了更好的管理分布式数据库而根据实际情况决定的。当然为了管理方便，可以将其等于互联网的域。

Global_name：对一个数据库（Oracle database）的唯一标识，oracle建议用此种方法命令数据库。该值是在创建数据库是决定的，缺省值为Db_name. Db_domain。在以后对参数文件中Db_name与Db_domain参数的任何修改不影响Global_name的值，如果要修改Global_name，只能用ALTER DATABASE RENAME GLOBAL_NAME TO <db_name.db_domain>命令进行修改，然后修改相应参数。

Service_name：该参数是oracle8i新引进的。在8i以前，我们用SID来表示标识数据库的一个实例，但是在Oracle的并行环境中，一个数据库对应多个实例，这样就需要多个网络服务名，设置繁琐。为了方便并行环境中的设置，引进了Service_name参数，该参数对应一个数据库，而不是一个实例，而且该参数有许多其它的好处。该参数的缺省值为Db_name. Db_domain，即等于Global_name。一个数据库可以对应多个Service_name，以便实现更灵活的配置。该参数与SID没有直接关系，即不必Service name 必须与SID一样。

Net service name：网络服务名，又可以称为数据库别名（database alias）。是客户端程序访问数据库时所需要，屏蔽了客户端如何连接到服务器端的细节，实现了数据库的位置透明的特性。

 

　实例 就是管理相关库的内存结构的名字（由SGA、PGA、服务器进程、用户进程、后台进程等组成）

　　数据库 就是实际的磁盘上的文件（数据文件、日志文件、控制文件等），负责保存数据，但由对应的实例来操作它的数据

　　服务名 就是对外公布的名称，为网络监听服务

　　其实，在我们传统的概念里，数据库是一个统称的名字，在Oracle中，你可以把“数据库”理解成一个大概念，也要把它理解成一个小概念

　　

　　1、一个Oracle数据库系统中可以同时安装几个数据库，每一个数据库对应一个唯

　　一的实例，但是OPS系统除外，可以多个实例同时对一个数据库操作，称为并行服务

　　器

　　2、只是一个名字，SID即是INSTANCE_NAME，SERVICE_NAMES主要用在监听器中，

　　为了方便吧，有些是为了传统习惯的延续，有些是为了更方便的使用

　　3、NET EASY CONFIG操纵的应该是主机字符串，是为客户端服务的

　　一个数据库可以对外公布多个服务名（SERVICE_NAMES）

　　一个客户端也可以用多个主机字符串连接到同一个数据库服务器上

　　4、一个OS上可以装多个Oracle数据库（小的概念），每个库可以对外公布多个服

　　务名，都通过init.ora和listener.ora来实现

-----------------------------------------------------

1建立net 服务名

linux下 进入

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

添加

CCPBS_19 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.38.19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CCPBS)
    )
)

保存 退出 然后测试下

>tnsping ccpbs_19

 

TNS Ping Utility for Linux: Version 9.2.0.7.0 - Production on 02-JUL-2009 15:16:52

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.38.19)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CCPBS)))
OK (20 msec)

测试成功

Windows下的参见 http://article.pchome.net/content-340501.html

 

2、创建数据库链接，

 

/home/oracle/>sqlplus /nolog

SQL*Plus: Release 9.2.0.7.0 - Production on Thu Jul 2 16:01:00 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn /as sysdba
Connected.

进入系统管理员SQL>操作符下，运行命令：

>create public database link ecbm_19 connect to ecbm_userName identified by ecbm_pwd using 'CCPBS_19';

Database link created.

格式:

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

　　则创建了一个连接到10.130.38.19 ccpbs上以ecbm_userName 用户的连接

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

删除 DROP PUBLIC DATABASE LINK ecbm_19;

 

 

　3、建立同义词，为了使有关分布式操作更透明，ORACLE数据库里有同义词的对象synonym

　　

　　SQL>create synonym bjscottemp for emp@beijing;

　　

　　于是就可以用bjscottemp来替代带@符号的分布式链接操作emp@beijing。

　　

create public synonym poh for po_headers_all@erp_danzheng_test;

create public synonym papf for per_all_people@erp_danzheng_test;

create public synonym pv for po_vendors@erp_danzheng_test;

 

 

　4、查看所有的数据库链接，进入系统管理员SQL>操作符下，运行命令：

　　

　　SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';

 

参考 http://moonsoft.itpub.net/post/15182/125559

 

 

测试条件：
假设某公司总部在北京，新疆有其下属的一个分公司。在本次测试中，新疆的计算机为本地计算机，即本要的IP地址为：192.168.1.100

北京的总部有一个集中的数据库，其SID是SIDBJ，用户名：userbj,密码：bj123,北京的IP地址是：192.168.1.101。
在本地(新疆)的分公司也有一个数据库，其SID是SIDXJ，用户：userxj，密码：xj123，新疆的IP地址是：192.168.1.100。
要将本地新疆的SIDXJ数据库中访问到北京的数据库SIDBJ中的数据。
也就是说，在sidxj的数据库中，用户userxj(192.168.1.100)需要建立DBLINK，以userbj的用户身份访问sidBJ(192.168.1.101)中的数据。

测试环境：两个数据库均建立在WINXP上，ORACLE的版本均为Oracle817
建立环境时，要注意关闭两台计算机上的Windows的防火墙，否则，会出现能ping通，但Oracle连接不通的情况。

1、问：如何返回数据库的GLOBAL_NAME？
执行SELECT * FROM GLOBAL_NAME;
北京的数据库的GLOBAL_NAME为SIDBJ.US.ORACLE.COM
新疆的数据库的GLOBAL_NAME为SIDXJ

2、问：如何查看Global_name参数是true还是False?
答：执行：SQL> show parameter global_name;
执行的结果如下：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

表示该参数是true.

该参数为true时，你在本地建立的DBLINK的名称必须和远程的Global_name一致才行。

3、问：查看远程数据徊是否支持高级复制功能。

答：通过查看v$option视图，如果其中Advanced replication为TRUE，则支持高级复制功能；否则不支持。
执行语句为：select * from v$option;

也可以执行select * from v$option where PARAMETER='Advanced replication'语句，
如何返回值为True，那么就是支持，否则就是不支持。在两个数据库中都是检查是否支持才行。

建立步骤：

1、在本地建立一个Oracle的客户端连接tns_xj_to_bj，用于连接北京的数据库。

在北京的数据库中，建立一个表用于测试。
create table USERBJ.BJ_TEST
(
STU_ID NUMBER,
STU_NAME VARCHAR2(100)
)
在其中增加一条记录：
insert into BJ_TEST (STU_ID, STU_NAME)
values (1, '钟德荣');

 

 

2、新建一个连接tnsxj，用于连接本地的sidxj数据库，以tnsxj/userxj/xj123登录到PLSQL中。

下面开始创建连接到北京的远程数据连接DBLink。
create database link SIDBJ.US.ORACLE.COM connect to userbj identified by bj123 using 'tns_xj_to_bj';
其中：SIDBJ.US.ORACLE.COM是远程的数据库的global_name,userbj是连接SIDBJ的用户名，bj123是userbj的密码，
tns_xj_to_bj是本地建立的连接到北京的数据库的服务名。

3、测试连接是否成功：
select * from dual@SIDBJ.US.ORACLE.COM
如果返回结果如下则表示连接成功了。
DUMMY
-----
X

4、在本地数据为中查询已经建立的远程连接名：
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------
SYSTEM SIDBJ.US.ORACLE.COM
USERXJ DBLINK_XJ_TO_BJ
USERXJ SIDBJ

5、至此，在新疆的计算机上建立了一个DBLINK，用于连接到北京的数据库上，
在新疆的数据库中查询北京的数据库中表的信息：
select * from bj_test@SIDBJ.US.ORACLE.COM
查询结果：
STU_ID STU_NAME
---------- --------------------------------------------------------------------------------
1 钟德荣

表示查询是正常的。

6、关于global_name参数的测试

create database link a connect to userbj identified by bj123 using 'tns_xj_to_bj';
建立了一个DBLINK，执行下面的查询：
select owner,object_name from dba_objects where object_type='DATABASE LINK';
可以看到已经建立了名为：a的DBLINK。
但执行下面的查询，检索远程数据时，就会出现错误。
select * from bj_test@a
原因是因为本地数据库的global_names的参数值为true，所以，dblink的名称必须和远程数据库的global_names相同。
要修改本地数据库的global_names参数为false;(注意是修改本地数据库SIDXJ，通过测试修改过程的参数不行，过程的global_names参数为TRUE还是为FALSE没有关系)
使用下面的语句修改该参数：
SQL> alter system set global_names=false;

再执行下面的几步即可查询出数据：
create database link a connect to userbj identified by bj123 using 'tns_xj_to_bj'; --创建DBLINK

select owner,object_name from dba_objects where object_type='DATABASE LINK';--查询现有的DBLINK

select * from bj_test@a--查询远程表

7、下面在SIDXJ中建立存储过程，通过DBLINK检索远程数据库SIDBJ，存储过程如下：

CREATE OR REPLACE PROCEDURE test_cur
as
strSql1 varchar2(1000);
t_stu_name varchar2(100);
TYPE TCUR IS REF CURSOR;
CUR TCUR;

begin
strSql1:='select stu_name from bj_test@a';
OPEN CUR FOR strSql1;
LOOP
FETCH CUR INTO t_stu_name;
EXIT WHEN CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(t_stu_name);

END LOOP;
CLOSE CUR;
end test_cur;

测试存储过程显示，输出了过程数据库中的STU_NAME的值。

以上脚本全部在ORACLE817下通过测试。