Oracle创建dblink,可赋值直接运行

-- 查看用户是否具备创建database link权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='用户名';

-- 如果没有权限,则给用户授予创建dblink的权限
grant create public database link, drop public database link to 用户名;

-- 查询现有dblink
select owner,object_name from dba_objects where object_type='DATABASE LINK';

-- 删除dblink
drop public database link dnlink名称;

创建dblink有两种形式,一种是把描述写在tnsnames.ora中,一种是把描述写在创建语句中.

第一种:
在tnsnames.ora添加配置

目标服务器的Oracle服务名 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 主机名或url)(PORT = 端口))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 目标服务器的Oracle服务名)
    )
  )

创建语句如下:

create public database link dnlink名称 
connect to 用户 identified by "密码" 
using '目标服务器的Oracle服务名';

第二种:

create public database link dnlink名称 
connect to 用户 identified by "密码" 
using 
'  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 主机名或url)(PORT = 端口))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 目标服务器的Oracle服务名)
    )
  )';

-- 跨库查询
select * from 表名@dnlink名称