dblink(Database Link)
数据库链接顾名思义就是数据库的链接,就像电话线一样,是一个通道,当我们要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
1.创建
创建dblink前,首先确认该用户是否有创建dblink的权限。
若无,则授权:
#sysdba登录
GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to user;
创建dblink语法:
CREATE [SHARED] [PUBLIC] database link link_name
[CONNECT TO
[user] [current_user]
IDENTIFIED BY password]
[AUTHENTICATED BY user IDENTIFIED BY password]
[USING 'connect_string']
eg:
CREATE PUBLIC DATABASE LINK jinan_to_beijing
CONNECT TO beijing_name IDENTIFIED BY beijing_passwd
USING 'to_beijing_sid_name';
或
CREATE PUBLIC DATABASE LINK jinan_to_beijing
CONNECT TO beijing_name IDENTIFIED BY beijing_passwd
USING
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = beijing_host_ip)
(PORT = 1521)
)
)
(CONNECT_DATA =
#(SERVER = DEDICATED)
(SERVICE_NAME/SID_NAME = to_beijing_sid_name)
)
)';
测试是否创建成功,有返回值表示成功:
select * from dual@jinan_to_beijing;
查询:
SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';
SQL>select * from all_db_links;
删除:
DROP DATABASE LINK dblink_name ;
2.使用
类似于直接查表的方式:
SELECT * FROM TBL_NAME@DBLINK_NMAE ;