select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); --查看是否有创建dblink的权限
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to system; --赋予相关的权限给system用户
---------------------------------------------------------------------------------------------------------------
--如果已经在本地数据库tnsnames.ora文件中配置了要远程访问的数据库
create public database link dblink_name connect to user identified by password using '远程数据库的实例名';
--dblink_name:要创建的dblink名称
--user/password:需要在本地登录到远程数据库的用户名/密码
---------------------------------------------------------------------------------------------------------------
--如果没有在本地数据库tnsnames.ora文件中配置要远程访问的数据库
create public database link dblink_name
connect to user identified by password
using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =远程数据库的ip)(PORT = 远程数据库的端口号))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 远程数据库的实例名))
)';
--dblink_name:要创建的dblink名称
--user/password:需要在本地登录到远程数据库的用户名/密码
-----------------------------------------------------------------------------------------------------------
--删除dblink
drop public database link dblink_name;
------------------------------------------------------------------------------------------------------
--查看
SELECT * FROM ALL_DB_LINKS;