概述

DB_LINK也是監(jiān)聽網(wǎng)絡(luò)的一種， 是從一個數(shù)據(jù)庫到另一個數(shù)據(jù)庫的一個鏈接，就是監(jiān)聽網(wǎng)絡(luò)，配置DB_LINK需要創(chuàng)建相應(yīng)的權(quán)限。

1.私有dblink

配置tns

ora11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db01)
    )
  )

在test用戶下創(chuàng)建私有dblink

22:33:05 TEST@db01> create database link ora11g connect to test identified by "test" using 'ora11g';

Database link created.


22:33:18 TEST@db01> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                OBJECT_NAME
-------------------- ------------------------------
TEST                 ORA11G

在Test用戶下使用dblink查詢遠(yuǎn)程數(shù)據(jù)庫

22:34:13 TEST@db01> select * from test.test01@ora11g;

        ID NAME
---------- --------------------------------------------------
         1 test01
         2 test02
         3 test03


22:34:24 TEST@db01> conn / as sysdba
Connected.

在sys用戶下不可以查詢，在TEST用戶下創(chuàng)建的私有dblink只有該用戶擁有權(quán)限

22:34:32 SYS@db01> select * from test.test01@ora11g;
select * from test.test01@ora11g
                          *
ERROR at line 1:
ORA-02019: connection description for remote database not found

刪除私有dbLink，只有創(chuàng)建私有dblink的用戶才可以去刪除，其他用戶不能刪除

23:42:41 SYS@db01> drop database link ORA11G;
drop database link ORA11G
                   *
ERROR at line 1:
ORA-02024: database link not found    ---------sys用戶下不行



23:42:57 SYS@db01> conn test/test
Connected.

TEST用戶下才可以正常刪除
23:43:07 TEST@db01> drop database link ORA11G;

Database link dropped.   

2.公有dblink

tns跟前面一樣
創(chuàng)建公有dblink

22:32:39 TEST@db01> create public database link ora11g connect to test identified by "test" using 'ora11g';

Database link created.

Elapsed: 00:00:00.01
22:32:46 TEST@db01> 
22:32:53 TEST@db01> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                OBJECT_NAME
-------------------- ------------------------------
PUBLIC               ORA11G

所有用戶下都可以查詢遠(yuǎn)程數(shù)據(jù)庫

23:44:52 SYS@db01>  select * from test.test01@ora11g;

        ID NAME
---------- --------------------------------------------------
         1 test01
         2 test02
         3 test03

Elapsed: 00:00:00.03
23:45:04 SYS@db01> conn test/test    
Connected.
23:45:11 TEST@db01>  select * from test.test01@ora11g;

        ID NAME
---------- --------------------------------------------------
         1 test01
         2 test02
         3 test03

刪除公有dblink

22:32:55 TEST@db01> drop public database link ora11g;

Database link dropped.

3.全局dblink，類似公有dblink只是不需要去配置tnsname.ora

create public database link ora11g connect to test identified by "test" using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
) (CONNECT_DATA =
(SERVICE_NAME = db01)
))';