Oracle数据库创建dblink的过程及其用法详解

原创已于 2024-11-17 10:45:18 修改 · 9.3k 阅读

32 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2024-11-16 23:54:50 首次发布

JavaWeb技术同时被 2 个专栏收录

62 篇文章

订阅专栏

JavaEE阶段

34 篇文章

订阅专栏

前言

‌dblink是Oracle数据库中用于连接不同数据库实例的一种机制‌。通过dblink，用户可以在一个数据库实例中直接查询或操作另一个数据库实例中的表、视图或存储过程。‌

dblink的作用主要体现在以下几个方面：

‌跨数据库操作‌：允许用户在一个数据库实例中直接访问另一个数据库实例中的数据。
‌简化数据管理‌：通过dblink，可以方便地管理和维护分布在多个数据库中的数据，而无需在每个数据库中重复相同的数据操作。
‌提高效率‌：通过dblink，可以减少数据传输的延迟，提高数据处理的效率。

一、dblink介绍

dblink(Database Link)数据库链接，顾名思义就是数据库的链接，就像电话线一样，是一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

使用 CREATE DATABASE LINK 语句来创建 DBLINK。在创建过程中，需要指定目标数据库实例的连接信息，如用户名、密码、数据库名等。

CREATE DATABASE LINK dblink_name  
CONNECT TO username IDENTIFIED BY password  
USING 'tns_name';

其中，tns_name 是目标数据库实例在 tnsnames.ora 文件中配置的别名。

一旦 DBLINK 创建成功，就可以通过它来访问目标数据库实例中的对象了。在 SQL 查询中，可以使用 @dblink_name 的语法来指定要查询的数据库实例。

SELECT * FROM table_name@dblink_name;

实例

CREATE DATABASE LINK dblink_name  
CONNECT TO PUTRASIT IDENTIFIED BY rasitt
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

权限：创建数据库链接的账号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限，用来登录到远程数据库的账号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK权限在DBA中）。一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的。

二、创建dblink

创建dblink一般有两种方式，不过在创建dblink之前用户必须有创建dblink的权限。想知道有关dblink的权限，以sys用户登录到本地数据库：

select * from user_sys_privs t where t.privilege like upper('%link%');

查询结果集：

1 SYS CREATE DATABASE LINK NO

2 SYS DROP PUBLIC DATABASE LINK NO

3 SYS CREATE PUBLIC DATABASE LINK NO

在数据库中dblink有三种权限：

1.CREATE DATABASE LINK（所创建的dblink只能是创建者能使用，别的用户使用不了） ，

2.CREATE PUBLIC DATABASE LINK（public表示所创建的dblink所有用户都可以使用），

3.DROP PUBLIC DATABASE LINK。

在sys用户下，把CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASELINK权限授予给你的用户：

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;

然后以scott用户登录本地数据库。

1）已经配置本地服务

create public database link link_name

connect to username identified by password

using 'connect_string';

注意：link_name是连接名字，可以自定义；

username是登陆数据库的用户名；

password是登陆数据库的用户密码；

connect_string是数据库连接字符串。

数据库连接字符串是当前客户端数据库中TNSNAMES.ORA文件里定义的别名名称。

2）直接建立链接

create database link link_name

connect to username identified by password

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X )(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = SSID)

)

)';

host=数据库的ip地址，service_name=数据库的ssid。其实两种方法配置dblink是差不多的，个人感觉还是第二种方法比较好，这样不受本地服务的影响。注意: 假如创建全局dblink，则必须使用systm或sys用户，在database前加public。

三、dblink查询

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

select * from dba_db_links;

dblink删除

DROP PUBLIC DATABASE LINK link_name;

dblink使用

SELECT……FROM 表名@数据库链接名;

查询、删除和插入数据和操作本地的数据库是一样的，只不过表名需要写成“表名@dblink服务器”而已。

案例：查询上海数据库中emp表数据

select * from emp@ShangHai;

设此处上海数据库的数据库连接字符串为ShangHai；

dblink应用举例

当需要跨库拷贝数据且数据量很大的时候，使用dblink速度很快。
案例：通过dblink跨库复制数据量非常多的表T_USER_LOG_INFO

create table test as select * from T_USER_LOG_INFO@db2

同义词配合

例子中from emp@ShangHai可以创建同义词来替代

CREATE SYNONYM 同义词名 FOR 表名;

CREATE SYNONYM 同义词名 FOR 表名@数据库链接名;

案例

create synonym sh_scott_emp for emp@ShangHai;

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

dblink是独立于创建用户（USER_DB_LINKS的USERNAME）起作用的，其他用户无法使用这个连接，无权限也不能删除它。

需要注意的是在oracle参数中，有一个参数叫global_names，如果该参数为true，那么在使用db link时，dblink的名字一定要和被访数据库实例名一致，否则会报ORA-2085错。

四、注意事项

权限：创建和使用 dblink需要相应的权限。通常，只有数据库管理员或具有相应权限的用户才能执行这些操作。
安全性：使用 dblink时需要注意安全性问题，特别是当涉及到敏感数据或跨信任域的数据库连接时。
性能：跨库查询可能会受到网络延迟、数据库性能等多种因素的影响，因此在使用dblink时需要考虑到这些因素对性能的影响。

总之，dblink是 Oracle 数据库中实现跨库查询和数据交换的重要机制，对于需要在多个数据库实例之间进行交互的应用场景非常有用。