oracle dblink的创建和使用

1.查看dblink

sys用户执行：

select * from dba_db_links;

或
select owner,object_name from dba_objects where object_type='DATABASE LINK';

2.创建dblink

用户授权：

创建dblink的用户必须有对应的数据库权限create public database link 或者create database link 

 select * from session_privs ;
  select * from user_sys_privs; 

【若无权限，执行：grantcreatepublicdatabase link,createdatabase linkto USERNAME;】

创建dblink语句：
create database link dblinkname connect to touser identified by pwd using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =orcl)
)
)';        

3.dblink查询

select * from touser.tablename@dblinkname;

4.删除dblink

    drop database link dblinkname;