如何進行oracle create database link_數據庫鏈接測試

這篇文章將為大家詳細講解有關如何進行oracle create database 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;

關于如何進行oracle create database link_數據庫鏈接測試就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。