Oracle dblink 创建简录

本博文只是dblink的创建简录，曾经使用到的dblink场景不多
一些注意点，诸如权限、同名dblink优先级、GLOBAL_NAMES参数的影响不做讨论

用户级别的私有dblink：

conn / as sysdba
grant create database link to vincent;
-- 非DBA用户授权
conn vincent/vincent
create database link loc2sor
  connect to system identified by oracle
  using '(DESCRIPTION =
          (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.251)(PORT = 1521)))
          (CONNECT_DATA = (SERVICE_NAME = xxx)))';
-- 创建私有dblink
select count(*) from dba_users@loc2sor;
-- 测试

公共的dblink：

conn / as sysdba
create public database link pbdbl_loc2sor
  connect to system identified by oracle
  using '(DESCRIPTION =
          (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.251)(PORT = 1521)))
          (CONNECT_DATA = (SERVICE_NAME = xxx)))';
-- 创建公共dblink
conn vincent/vincent
select count(*) from dba_users@pbdbl_loc2sor;
-- 所有用户不用授权均可使用公共dblink

删除：

conn / as sysdba
drop public database link pbdbl_loc2sor;
drop database link vincent.loc2sor;