大数据-玩转数据-oracle创建dblink及应用

一、创建DBLINK的应用场景

oracle在进行跨库访问时，可以通过创建dblink实现。

二、创建DBLINK应用场景

在tnsnames.ora中配置两个数据库别名：orcl(用户名：wangyong 密码：1988)、orcl2(用户名：wangyong 密码：123456)，在orcl中 创建database link来访问orcl2

三、赋予权限

在创建database link之前，我们需要判断，登陆的用户是否具备创建database link 的权限，所以我们执行以下的语句(用wangyong用户登陆orcl)：

– 查看wangyong用户是否具备创建database link 权限

select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='WANGYONG';

如果查询有返回行，则表示具备创建database link权限，否则，则需要使用sys登陆orcl为WANGYONG用户赋予创建权限

– 给wangyong用户授予创建dblink的权限

grant create public database link to wangyong;

此时，再执行上面查看是否具备权限的sql语句，会发现有返回行，表示，WANGYONG这个用户已经具备创建database link的权限

四、创建DBLINK

create public database link TESTLINK2 connect to WANGYONG identified by "123456" USING 'ORCL21'

或：

create public database link TESTLINK2 
connect to  WANGYONG 
identified by "123456"  
using '
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )
';

五、删除dblink

drop public database link TESTLINK2 ;

六、解释

database link 后面跟DBLINK的名称，可以随便命名，有意义即可；

connect to 后面跟目标数据库的用户名，无需使用引号括起来。

identified by 后面跟目标数据库的密码，需要使用双引号括起来。

HOST = 后面跟 目标数据库的IP。

PORT = 后面跟目标数据库的端口号。

SERVICE_NAME = 后面跟目标数据库的名字。

ps：using后的面语句的格式需要注意，注意其中的空格，单个空格键，错误的空格会导致创建的DBLINK无效。

七、使用DBLINK增删改查：

–将目标数据库表中的数据在当前数据库中进行备份：

CREATE TABLE USERINFO AS SELECT * FROM USERINFO@TESTLINK2 

–查询目标数据库中的USERINFO表数据：

SELECT * FROM USERINFO@TESTLINK2