1.查看dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from dba_db_links;
2.创建dblink
创建dblink的用户应该有:
create public database link 或者create database link
--授权
grant create public database link,create database link to SCOTT;
--创建dblink(如果不加public,则创建的dblink只有创建者可以使用)
create public database link dblinkname connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';
3.使用dblink
比如创建了一个scott_select的public dblink(使用远程主机的SCOTT用户连接)
select * from scott.emp@scott_select; 查询scott用户下的emp表
4.删除dblink
drop public database link dblinkname;