oracle create database link_数据库链接测试

附录:
http://czmmiao.iteye.com/blog/1236562       --网上关于db link讲解

----创建db link语法
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password [ dblink_authentication ]
    }
  | dblink_authentication
  ]...
  [ USING connect_string ] ;

----如下为db link子句的语义
---如下子句必须与shared子句搭配使用
dblink_authentication

You can specify this clause only if you are creating a shared database link—that is,
you have specified the SHARED clause. Specify the username and password on the target instance.
This clause authenticates the user to the remote server and is required for security.
The specified username and password must be a valid username and password on the remote instance.
The username and password are used only for authentication. No other operations are performed on behalf of this user.

---如下子句
SHARED
--如指此子句创建基于单一网络连接(自源库到目标库)的db link,如此多个会话可以共享此db link,有点像shared server mode
Specify SHARED to create a database link that can be shared by multiple sessions using a single
network connection from the source database to the target database. In a shared server configuration,
----这种模式,一直保持一定数据的连接到目标库,防止过多的连接产生.但是,如多个客户端访问相同的本地模式对象时,共享的私有数据库连接非常在用
---因此使用相同的私有数据库连接
shared database links can keep the number of connections into the remote database from becoming too large.
Shared links are typically also public database links. However, a shared private database link can be useful
when many clients access the same local schema, and therefore use the same private database link.
--这种模式,源库的多个会话共享到目标库相同连接,即一个源库连接到目标库,另一个源库连接则断开;
In a shared database link, multiple sessions in the source database share the same connection to the target database.
Once a session is established on the target database, that session is disassociated from the connection, to make the
connection available to another session on the source database. To prevent an unauthorized session from attempting to
---为了防止非授权连接到目标库,你必须指定dblink_authentication仅允许授权用户访问目标库
connect through the database link, when you specify SHARED you must also specify the dblink_authentication clause for
the users authorized to use the database link.

---创建数据库链接
SQL> create database link dlink1 connect to   scott identified by system using 'orcl';

Database link created

--查询数据库链接信息
SQL> desc user_db_links;
Name     Type           Nullable Default Comments
-------- -------------- -------- ------- ----------------------------------
DB_LINK  VARCHAR2(128)                   Name of the database link
USERNAME VARCHAR2(30)   Y                Name of user to log on as
PASSWORD VARCHAR2(30)   Y                Deprecated-Password for logon
HOST     VARCHAR2(2000) Y                SQL*Net string for connect
CREATED  DATE                            Creation time of the database link

SQL> select * from user_db_links;

DB_LINK          USERNAME                       PASSWORD                       HOST              CREATED
-------------------------------------------------------------------- -----------
DLINK1             SCOTT                       orcl                              2013/1/25 1

SQL> select * from tab where rownum<=5;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BASE_BILL                      TABLE
BIN$NDy5NJ6AQ/C7STM+t8OG5A==$0 TABLE
BIN$aJswa+ULQ22uo7ykPIg6Vw==$0 TABLE
BIN$wEmpOM9LQValskI1dzyrqg==$0 TABLE
CLUSTER1                       CLUSTER

---测试数据库链接是否正常
SQL> select * from base_bill@dlink1 where rownum<=2;  --可查询结果

---存储过程中测试数据库链接
SQL> create or replace procedure proc_database_link
  2  as
  3  v_link varchar2(1000);
  4  begin
  5  select bill_name into v_link from base_bill@dlink1 where rownum=1;
  6  dbms_output.put_line(v_link);

PL/SQL procedure successfully completed

SQL> create table t_tb(a varchar2(1000));

---连接到另一个system用户
SQL> conn system/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@orcl

SQL> show user
User is "system"
---在scott用户创建的database link在system用户不可用,即create database link创建的数据库链接仅创建用户可用
SQL> select bill_name  from base_bill@dlink1 where rownum=1;

select bill_name  from base_bill@dlink1 where rownum=1

ORA-02019: connection description for remote database not found

---重连scott用户
SQL> conn scott/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@orcl

---删除dlink1数据库链接
SQL> drop database link dlink1;

Database link dropped

SQL>
SQL>
---创建公共数据库链接,所谓即库所有用户皆可使用此数据库链接
SQL> create public database link dlink1 connect to   scott identified by system using 'orcl';

Database link created

SQL> show user
User is "scott"
---当前创建用户可用
SQL> select bill_name  from base_bill@dlink1 where rownum=1;

BILL_NAME
--------------------------------------------------------------------------------
1
---再次连接到system用户
SQL> conn system/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@orcl
---system用户也可用
SQL> select bill_name  from base_bill@dlink1 where rownum=1;

BILL_NAME
--------------------------------------------------------------------------------
1

SQL>

---私有与公共database link的区别

-----公共(注:公共owner是public)
SQL> select * from dba_db_links;

OWNER
----------
PUBLIC

---私有 (注:私有owner是创建database link的用户)
SQL> select * from dba_db_links;

OWNER
---------
SCOTT

---current_user创建的db link
SQL> create  database link dlink1 connect to   current_user;

Database link created

SQL> desc T_A;
Name Type          Nullable Default Comments
---- ------------- -------- ------- --------
A    VARCHAR2(100) Y

--运行报如下错误
SQL> select * from t_a@dlink1 where rownum=1;

select * from t_a@dlink1 where rownum=1

ORA-02019: connection description for remote database not found

---如下2参数控制一个参数或会话可以同时最大打开的db link数量
SQL> show parameter open_link

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

---连续创建5个db link
SQL> create public database link dlink1  connect to   scott identified by system using 'orcl';

Database link created

SQL>
SQL> create public database link dlink2  connect to   scott identified by system using 'orcl';

Database link created

SQL> create public database link dlink3  connect to   scott identified by system using 'orcl';

Database link created

SQL> create public database link dlink4  connect to   scott identified by system using 'orcl';

Database link created

SQL> create public database link dlink5  connect to   scott identified by system using 'orcl';

Database link created

SQL> desc t_a;
Name Type          Nullable Default Comments
---- ------------- -------- ------- --------
A    VARCHAR2(100) Y

SQL> update t_a@dlink1 set a=10 where rownum=1;

1 row updated

SQL> update t_a@dlink2 set a=10 where rownum=1;

1 row updated

SQL> update t_a@dlink3 set a=10 where rownum=1;

1 row updated

SQL> update t_a@dlink4 set a=10 where rownum=1;

1 row updated

---当打开第5个db link报错
SQL> update t_a@dlink5 set a=10 where rownum=1;

update t_a@dlink5 set a=10 where rownum=1

ORA-02020: too many database links in use

---如不指定connect to identified by 则the database link uses the user name and password of each user who is connected to the database.
-----This is called a connected user database link. 即连接到远程库的每一个用户的用户名和密码尝试进行连接
SQL> create public database link dlink6 using 'orcl';

Database link created

SQL> rollback;

Rollback complete

SQL> select * from  t_a@dlink6 where rownum=1;

A
--------------------------------------------------------------------------------
10

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

转载于:http://blog.itpub.net/9240380/viewspace-753350/