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 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 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
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as

SQL> show user
User is "system"
---在scott使用者建立的database link在system使用者不可用,即create database link建立的資料庫連結僅建立使用者可用
SQL> select bill_name  from where rownum=1;

select bill_name  from where rownum=1

ORA-02019: connection description for remote database not found

---重連scott使用者
SQL> conn
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as

---刪除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 where rownum=1;

BILL_NAME
--------------------------------------------------------------------------------
1
---再次連線到system使用者
SQL> conn
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as
---system使用者也可用
SQL> select bill_name  from 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 where rownum=1;

select * from 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 set a=10 where rownum=1;

1 row updated

SQL> update set a=10 where rownum=1;

1 row updated

SQL> update set a=10 where rownum=1;

1 row updated

SQL> update set a=10 where rownum=1;

1 row updated

---當開啟第5個db link報錯
SQL> update set a=10 where rownum=1;

update 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  where rownum=1;

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