概述
DB_LINK也是监听网络的一种， 是从一个数据库到另一个数据库的一个链接，就是监听网络，配置DB_LINK需要创建相应的权限。
1.私有dblink
配置tns
ora11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01)
)
)
在test用户下创建私有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查询远程数据库
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用户下创建的私有dblink只有该用户拥有权限
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，只有创建私有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跟前面一样
创建公有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
所有用户下都可以查询远程数据库
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)
))';
