一、DBlink的作用DBLinkDatabase Link是 Oracle 数据库中的一个 Schema 对象用于从一个数据库访问另一个数据库中的表、视图或 PL/SQL 对象。创建后可以通过在对象名后追加 dblink 来引用远程对象如同访问本地对象一样二、前期准备1、查看DBlink创建权限若查看为空则需要赋予相关的权限-- 当前用户拥有的系统权限SELECT*FROMuser_sys_privsWHEREprivilegeLIKEupper(%DATABASE%);权限类型所需系统权限执行/授予位置授予对象说明创建私有 DBLinkCREATE DATABASE LINK本地数据库创建 DBLink 的本地用户仅创建者可使用该 DBLink创建公有 DBLinkCREATE PUBLIC DATABASE LINK本地数据库创建 DBLink 的本地用户所有本地用户均可使用该 DBLink连接远程数据库CREATE SESSION远程数据库CONNECT TO指定的远程用户远程用户必须具备否则使用 DBLink 时报 ORA-01045前两个权限决定“你能否在本地创建 DBLink”。第三个权限决定“DBLink 创建后能否成功连接到远程数据库”。创建 DBLink 时Oracle 通常不会立即验证远程连接只存储连接信息所以缺少 CREATE SESSION 往往在使用 DBLink 时才报错。2、赋予DBlink创建权限本地数据库授予权限需使用 DBA 或 SYSDBA 账号执行-- 授予创建私有 DBLink 的权限GRANTCREATEDATABASELINKTO授权的本地用户名;-- 授予创建公有 DBLink 的权限GRANTCREATEPUBLICDATABASELINKTO授权的本地用户名;-- 授予删除公有 DBLink 的权限如需GRANTDROPPUBLICDATABASELINKTO授权的本地用户名;检查当前用户是否已有权限select*fromuser_sys_privswhereprivilegelikeupper(%DATABASE%);可以看到相关权限已经赋权3、远程用户登陆权限执行数据库远程数据库 REMOTEDB执行用户DBA / SYSDBA在本地数据库以远程数据库用户remote_user登陆远程数据库-- 【远程数据库 REMOTEDB】以 DBA 登录执行CREATEUSERremote_user IDENTIFIEDBYremote_password;-- 【远程数据库 REMOTEDB】以 DBA 登录执行GRANTCREATESESSIONTOremote_user;-- 【远程数据库 REMOTEDB】以对象拥有者或 DBA 登录执行--说明对象权限控制 DBLink 能对远程对象做什么操作。--如果只查询授予 SELECT 即可如果需要写入再授予相应 DML 权限GRANTSELECT,INSERT,UPDATE,DELETEONremote_schema.remote_tableTOremote_user;4、网络配置创建 DBLink 前需确保本地和远程数据库均安装 Oracle Net且网络连通。建议先在本地数据库服务器上直接用远程账号测试连通性如果这一步成功说明网络、监听端口、Service Name 和远端账号认证均正常。sqlplus remote_user//10.x.x.x:1521/remote_service三、本地库创建DBlink方式一TNS别名在 $ORACLE_HOME/network/admin/tnsnames.ora 文件中添加条目#DBLINK_TARGET自定义的 TNS 别名大小写不敏感默认可包含字母、数字、下划线。建议全大写便于识别。#DESCRIPTION描述连接的网络地址和连接数据是一个嵌套结构。#ADDRESS网络地址指定远程数据库监听器的协议、主机和端口。可以有多个 ADDRESS用于故障转移FAILOVER或负载均衡LOAD_BALANCE。#CONNECT_DATA连接数据指定要连接的数据库服务。#SERVICE_NAME远程数据库的服务名DBLINK_TARGET(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST10.x.x.x)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEorcl)))使用 TNS 别名创建私有 DBLink--CREATE DATABASE LINK remote_db创建一个名为 remote_db 的数据库链接。省略 PUBLIC 表示私有 DBLink仅创建者可用--CONNECT TO remote_user指定登录远程数据库的用户名--IDENTIFIED BY remote_password指定远程用户的密码。密码用双引号包围可保留大小写和特殊字符--USING DBLINK_TARGET指定连接的远程数据库TNS配置中CREATEDATABASELINK remote_dbCONNECTTOremote_user IDENTIFIEDBYremote_passwordUSINGDBLINK_TARGET;方式二Easy Connect 格式无需配置 tnsnames.ora直接在 USING 子句中使用 host:port/service_name 格式--CREATE DATABASE LINK remote_db创建一个名为 remote_db 的数据库链接。省略 PUBLIC 表示私有 DBLink仅创建者可用--CONNECT TO remote_user指定登录远程数据库的用户名--IDENTIFIED BY remote_password指定远程用户的密码。密码用双引号包围可保留大小写和特殊字符--USING DBLINK_TARGET指定连接的远程数据库Easy Connect 格式CREATEDATABASELINK remote_dbCONNECTTOremote_user IDENTIFIEDBYremote_passwordUSING10.x.x.x:1521/orcl;场景三完整连接描述符注意如果密码以数字开头或包含特殊字符必须使用双引号包围否则会报错--CREATE DATABASE LINK remote_db创建一个名为 remote_db 的数据库链接。省略 PUBLIC 表示私有 DBLink仅创建者可用--CONNECT TO remote_user指定登录远程数据库的用户名--IDENTIFIED BY remote_password指定远程用户的密码。密码用双引号包围可保留大小写和特殊字符--USING指定连接的远程数据库完整连接描述符CREATEDATABASELINK remote_dbCONNECTTOremote_user IDENTIFIEDBYremote_passwordUSING(DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST 10.x.x.x)(PORT 1521)) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME orcl) ) );创建结果运行SQL语句查看dblink创建结果-- 查看当前用户可访问的所有 DBLinkSELECT*FROMALL_DB_LINKS;-- 查看当前用户拥有的 DBLinkSELECT*FROMUSER_DB_LINKS;-- 查看数据库中所有 DBLink需 DBA 权限SELECT*FROMDBA_DB_LINKS;视图字段介绍OWNER : DB Link的owner DB_LINK : DB Link名称主要使用 USERNAME : 利用DB link时登录远程数据库实例的用户名 HOST : 登录远程数据库的Netconnectstring CREATED : DB Link创建时间删除DBlinkdroppublicdatabaselink dblink_name;四、使用DBlink查询数据使用连接dblink进行数据查询--测试连通性SELECT*FROMDUALdblink_name;--正式查询--select * from 远程数据库表名远程连接dblink名;select*fromtable_namedblink_name;--如果连接远程库使用的是A用户但是要查询的表名属于B用户还需要在表名前增加用户名--select * from 表所属用户名.远程数据库表名远程连接dblink名;select*fromB.table_namedblink_name;