最近遇到在不同的数据库之间查询数据的问题,度娘了一下发现create database link。所以查看oracle sql reference后总结下子。
创建db link 有两种方式:
1.私有的(默认)--用户必须具有create database link 系统权限
create database link dbline01
connect to user_name identified by password -- user_name/password为远端的数据库用户名
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 库名)
)
)';
2.公有的 --用户必须具有create public database link 系统权限及远端数据库的create session 系统权限
create public database link dblink02
connect to user_name identified by password -- user_name/password为远端的数据库用户名
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 库名)
)
)';
连接成功后,测试
select * from SELECT * FROM t@dblink02; --正常显示数据 则成功!
删除 database link
drop database link dblink01;
drop public database link dblink02;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28758644/viewspace-1089083/,如需转载,请注明出处,否则将追究法律责任。