CREATE DATABASE LINK
- 使用 CREATE DATABASE LINK 语句来创建数据库链接。数据库链接是在一个数据库中,使您能够访问其它数据库对象的模式对象
在创建数据库链接之后,可以通过在表,视图 或 PL / SQL 对象名称后加上
@dblink
,来访问其他数据库中的表、视图 或 PL / SQL对象;可以使用 SELECT 语句查询其他数据库中的表或视图,也可以使用 INSERT,UPDATE,DELETE 或 LOCK TABLE 语句操作远程表和视图先决条件
- 要创建私有 dblink,必须具有 CREATE DATABASE LINK 系统权限
- 要创建公共 dblink,必须具有 CREATE PUBLIC DATABASE LINK 系统权限
- 另外,必须拥有远程 Oracle 数据库的 CREATE SESSION 系统权限
Oracle Net 必须安装在本地和远程 Oracle 数据库上
语法
PUBLIC
- 指定 PUBLIC 创建公共数据库链接,所有用户可用;如果省略,那么数据库链接是私有的,只对创建者可用
- 远程数据库上可访问的数据取决于连接到远程数据库时 dblink 使用的身份:
- 如果指定
CONNECT TO user IDENTIFIED BY password
,则 dblink 使用指定的用户和密码连接 - 如果指定
CONNECT TO CURRENT_USER
,那么 dblink 将根据链接的使用范围与有效的用户连接 如果省略这两个子句,则 dblink 将以本地连接的用户连接到远程数据库
SHARED
- 指定 SHARED 创建数据库链接,该链接使用从源库到目标库的单个网络连接来共享多个会话。在共享服务器配置中,共享数据库链接可以使连接到远程数据库的连接数量不会变得太大。共享链接通常也是公共数据库链接。但是,当多个客户端访问同一个本地 schema 时,共享私有数据库链接可能会很有用,因此使用同一个私有数据库链接
在共享数据库链接中,源库的多个会话共享目标库上的同一个连接。一旦在目标库上建立了一个会话,该会话将从连接中解除关联,从而使该连接可用于源库上的另一个会话。为防止未经授权的会话试图通过 dblink 进行连接,在指定 SHARED 时还必须为授权使用数据库链接的用户指定
dblink_authentication
子句dblink
- 指定数据库链接的完整名称或部分名称。如果仅指定数据库名称,则 Oracle 数据库隐式附加本地数据库的数据库域
- 仅支持 ASCII 字符的 dblink,不支持多字节字符
- 数据库链接名称不区分大小写,并以大写的 ASCII 字符存储;如果将数据库名称指定为带引号的标识符,则引号将被忽略
- 如果 GLOBAL_NAMES 初始化参数的值是TRUE,则 dblink 必须与它所连接的数据库具有相同的名称
- 在一个会话或 Oracle RAC 的一个实例中可以打开的最大 dblink 数取决于初始化参数OPEN_LINKS 和 OPEN_LINKS_PER_INSTANCE 的值
- 创建 dblink 的限制
- 不能在其他用户的 schema 中创建数据库链接,也不能使用 schema 的名称来限定 dblink
dblink 的名字中可以使用句点
.
,所以 Oracle 数据库将整个字符串(比如ralph.linktosales
)解释为数据库链接的名字,而不是 ralph 用户有一个数据库链接 linktosalesCONNECT TO 子句
CONNECT TO 子句允许您指定要用于连接到远程数据库的用户和凭证(如果有)
CURRENT_USER子句
- 指定 CURRENT_USER 创建当前用户数据库链接(current user database link),当前用户必须是在远程数据库上具有有效帐户的全局用户
- 如果直接使用 dblink 而不是从存储对象中使用,则 CURRENT_USER 就是所连接的用户
- 执行使用 dblink 的存储对象(如过程,视图或触发器)时,CURRENT_USER 是拥有该存储对象的用户,而不是调用该对象的用户。例如,如果 dblink 出现在过程 scott.p(由scott 创建),并且用户 jane 调用过程 scott.p,则 CURRENT_USER 是 scott
但是,如果存储对象是调用者权限的函数,过程或包,则调用者的授权 ID 被用作远程用户的连接。例如,如果 dblink 出现在过程 scott.p(scott 创建的一个调用者权限过程),并且用户 Jane 调用过程 scott.p,然后 CURRENT_USER 是 jane,使用 Jane 的权限执行存储过程
user IDENTIFIED BY passwd
使用固定用户数据库链接(fixed user database link)指定用于连接到远程数据库的用户名和密码。如果省略此子句,则 dblink 将使用连接到数据库的用户的用户名和密码,这称为连接用户数据库链接(connected user database link)
dblink_authentication
- 只有在创建共享数据库链接时(也就是说,指定了 SHARED 子句),才能指定此子句
指定目标实例上的用户名和密码。此子句将用户认证远到程服务器上,并且是安全所需的。指定的用户名和密码必须是远程实例上的有效用户名和密码。用户名和密码仅用于身份验证,不为该用户执行其他操作
定义一个固定用户 dblink
- remote 数据库中的 hr 用户定义了一个名为 local 的固定用户数据库链接,使用 local 数据库的 hr 用户连接
CREATE DATABASE LINK local
CONNECT TO hr IDENTIFIED BY password
USING 'local';
- 在创建这个数据库链接之后,hr 可以查询 local 数据库中 hr 的表:
SELECT * FROM employees @ local;
- 用户 hr 还可以使用 DML 语句来修改 local 数据库上 hr 用户的表数据:
INSERT INTO employees@local
(employee_id, last_name, email, hire_date, job_id)
VALUES (999, 'Claus', 'sclaus@example.com', SYSDATE, 'SH_CLERK');
UPDATE jobs@local SET min_salary = 3000
WHERE job_id = 'SH_CLERK';
DELETE FROM employees@local
WHERE employee_id = 999;
- 使用此固定数据库链接,remote 数据库上的 hr 用户还可以访问 local 数据库上其他用户的表。这条语句假定 hr 用户拥有 oe.customers 的 SELECT权限。该语句连接到 local 数据库的 hr 用户,然后查询 oe.customers 表:
SELECT * FROM oe.customers@local;
ALTER DATABASE LINK
- 当连接或认证用户的密码更改时,使用 ALTER DATABASE LINK 语句修改固定用户数据库链接
- 不能使用此语句来更改 dblink 的连接用户或认证用户;要更改 user,必须重新创建数据库链接
该语句仅适用于固定用户数据库链接,不适用于连接用户或当前用户数据库链接
先决条件
- 要更改私有数据库链接,必须具有 ALTER DATABASE LINK 系统权限
要更改公共数据库链接,必须具有 ALTER PUBLIC DATABASE LINK 系统权限
语法
- 示例
ALTER DATABASE LINK private_link
CONNECT TO hr IDENTIFIED BY hr_new_password;
ALTER PUBLIC DATABASE LINK public_link
CONNECT TO scott IDENTIFIED BY scott_new_password;
ALTER SHARED PUBLIC DATABASE LINK shared_pub_link
CONNECT TO scott IDENTIFIED BY scott_new_password
AUTHENTICATED BY hr IDENTIFIED BY hr_new_password;
DROP DATABASE LINK
使用 DROP DATABASE LINK 语句从数据库中删除数据库链接
先决条件
- 私有数据库链接必须位于自己的 schema 中,只有 owner 才可以删除
删除公共数据库链接,必须具有 DROP PUBLIC DATABASE LINK 系统权限
语法
- PUBLIC 指定要删除公共数据库链接
- dblink 指定要删除的数据库链接的名称
DROP [ PUBLIC ] DATABASE LINK dblink ;
删除 dblink 的限制
- 不能删除在另一个用户的 schema 中的数据库链接, 不能使用 schema 的名字来限定dblink ,因为 dblink 的名字中允许使用句点。因此 Oracle 数据库将整个字符串(如
ralph.linktosales
)解释为数据库链接的名字,,而不是 ralph 用户有一个数据库链接 linktosales - 删除公共数据库链接 remote
DROP PUBLIC DATABASE LINK remote;
查询 db_link 的信息
- 查询数据库中所有的 DBLINK
set linesize 200
COL OWNER FOR A15
COL DB_LINK FOR A25
COL HOST FOR A25
COL USERNAME FOR A15
SELECT * FROM DBA_DB_LINKS;
ORA-02082
- 原因:db link 的名字与数据库的名字相同
[oracle@hbdw2:/oratmp2]$ oerr ora 2082
02082, 00000, "a loopback database link must have a connection qualifier"
// *Cause: An attempt was made to create a database link with the same name
// as the current database.
// *Action: a loopback database link needs a trailing qualifier, for example
// MYDB.EXAMPLE.COM@INST1 - the '@INST1' is the qualifier