db link

在分布式数据库环境中global name是数据库的唯一标识
a global database name由db_name和db_domain组成
db_name和db_domain的命名规范
DB_NAME Must be eight characters or less. sales
DB_DOMAIN Must follow standard Internet conventions. Levels in domain names must be separated by dots and the order of domain names is from leaf to root, left to right.

global name存储在数据字典中
使用alter database rename global_name to  database.domain;来修改
这里的domain跟实例参数db_domain没有关系
--This domain is used when the link is created because it is the domain part of the global database name of the local database
--the value of the DB_DOMAIN initialization parameter is independent of the ALTER DATABASE RENAME GLOBAL_NAME statement
--The ALTER DATABASE statement determines the domain of the global database name, not
--the DB_DOMAIN initialization parameter (although it is good practice to alter DB_DOMAIN to reflect the new domain name).

create dblink
需要的权限有
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE SESSION
link的类型 及区别
private
public
global(已弃用)
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
连接类型的区别：
 If they are private, then only the user who created the link has access; --属创建用户所有
 if they are public, then all database users have access.--所有用户所有
连接用户的区别：
一、Connected user link 创建当前连接用户的db link 意味着远处主机由相同的用户
--Users connect as themselves, which means that they must have an account on the remote database with the same username as their account on the local database
语法：
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];
实验：
给hr sh解锁 赋权限
grant CREATE DATABASE LINK,CREATE PUBLIC DATABASE LINK,CREATE SESSION to sh
[1]rac1使用连接标识符  创建private link 连接rac2
SQL> create database link rac2  using 'orcl2';

Database link created.

SQL>  select * from user_db_links;

DB_LINK    USERNAME   PASSWORD   HOST       CREATED
---------- ---------- ---------- ---------- ---------
RAC2.COM                         orcl2      12-SEP-10

SQL> select * from global_name@rac2;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.COM

SQL> select * from hrtab@rac2;

no rows selected

SQL> conn sh/sh
Connected.
SQL> select * from shtab@rac2;
select * from shtab@rac2
                    *
ERROR at line 1:
ORA-02019: connection description for remote database not found
[2]rac1使用连接标识符  用户sh创建public link 连接rac2
SQL> create public  database link shrac2  using 'orcl2';

Database link created.

SQL> select * from shtab@shrac2;

no rows selected

SQL> conn hr/hr
Connected.
SQL> select * from hrtab@shrac2;

no rows selected
--尝试访问不是当期连接用户的对象时报错
SQL> select * from shtab@shrac2;
select * from shtab@shrac2
              *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from SHRAC2
/*********************************************************************
结论：本地和远程用户是一对一关系  张三只能访问张三 不能访问李四
私有的Connected user link 等同于私有的Fixed user link
***********************************************************************/

二、Fixed user link 固定用户连接
常用的连接方式 语法:
CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;
SQL> conn hr/hr
Connected.
SQL> create public database link phrrac2 connect to hr identified by hr using 'orcl2';
SQL> select * from dba_db_links;

OWNER                          DB_LINK    USERNAME   HOST       CREATED
------------------------------ ---------- ---------- ---------- ---------
PUBLIC                         PHRRAC2.COM HR         orcl2      12-SEP-10                              
PUBLIC                         SHRAC2.COM            orcl2      12-SEP-10
HR                             RAC2.COM              orcl2      12-SEP-10
SQL> select * from hrtab@phrrac2;

no rows selected
--任何用户都可以访问远程用户hr的对象

三、Current user link
Current user links are an aspect of Oracle Advanced Security.

A user connects as a global user. A local user can connect as a global user in the
context of a stored procedure, without storing the global user's password in a link
definition. For example, Jane can access a procedure that Scott wrote, accessing
Scott's account and Scott's schema on the hq database.
语法：
CREATE PUBLIC DATABASE LINK hq CONNECT TO CURRENT_USER using 'hq';

共享的db link
服务器进程和远程主机间的link 多个客户端进程能同时使用这个link
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.

同标准link的区别
1、不同用户访问相同远程对象是可以共享一个连接
2、可以重用相同服务器进程使用相同link建立的链接

•Different users accessing the same schema object through a database link can share a network connection.

•When a user needs to establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.

•When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.

ex:
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'

db link的命名
When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq is hq.acme.com, and GLOBAL_NAMES is TRUE, then the link name must be called hq.acme.com
如果global_names 设置成true则 link的名称必须和远程数据库的global_name一致。
否则报ora-02085

 

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_concepts.htm#i1007964
 

 

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_admin.htm#sthref4122

 

 

 

 

 

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

转载于:http://blog.itpub.net/21993926/viewspace-673595/