dblink的分类和使用

什么是dblink

Dblink是一个连接一个数据库服务和另一个数据库服务的链接。这个链接实际上是定义在数据字典表中的一个入口，为了使用dblink，我们需要链接进包含这个数据字典入口的本地数据库。每一个在分布式系统中的数据库都必须有唯一的global database name，global database name是识别分布式系统中数据库的唯一标识。比如scott用户使用dblink查找远程数据库数据，如下图

Features

Private database link

This link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database.

Public database link

When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database.

Global database link

When an Oracle network uses a directory server, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.

 

Private database link：只有创建语句中标识的用户才可以访问远程数据库

Public database link：本地数据的所有用户都可以访问远程数据库

Global database link：需要创建global user，在所有分布式数据库中都可以使用global user访问远程数据库

以认证用户可以把dblink分成以下3类

User Type	Description	Sample Link Creation Syntax
Connected user	A local user accessing a database link in which no fixed username and password have been specified. If SYSTEM accesses a public link in a query, then the connected user is SYSTEM, and the database connects to the SYSTEM schema in the remote database. Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link.	CREATE PUBLIC DATABASE LINK hq 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.	CREATE PUBLIC DATABASE LINK hq CONNECT TO CURRENT_USER using 'hq';
Fixed user	A user whose username/password is part of the link definition. If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.	CREATE PUBLIC DATABASE LINK hq CONNECT TO jane IDENTIFIED BY password USING 'hq';

Connected user：不固定用户，当用户连接到远程数据库时，用户在远程数据库是哪个的角色仍然是跟当前用户名相同的用户

Current user：使用当前用户创建当前用户的dblink

Fixed user：固定用户。在dblink创建语句中定义了远程数据库的连接用户，连接进去的用户是固定的。

我们来创建一个固定用户的private dblink

CREATE DATABASE LINK TEMP_DBLINK1 connect to scott identified by tiger USING '(DESCRIPTION=(LOAD_BALANCE=yes)(FAILOVER=no)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP

)(HOST=10.192.208.58)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.192.208.57)(P

ORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)))';  

使用方法:在远程数据库对象上加@db_link

Select * from scott.trail_1@ TEMP_DBLINK1 where rownum<=2

TRIALNO

-------

      1

      2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2140872/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2140872/