003-oracle 跨 oracle 库查询

oracle 跨 oracle 库查询

当用户需要跨本地数据库访问另外一个数据库表中的数据时，本地数据库中必须创建远程数据库的DBLINK，通过 DBLINK 可以像访问本地数据库一样访问远程数据库表中的数据。其实，oracle 不仅可以跨同类型的数据库，还可以跨不同类型的数据库，如 MySQL。

1、如何创建 DBLINK

官方文档：https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

在创建 DBLINK 之前，普通用户必须具有相关的权限才能创建 DBLINK，以 SYS 用户登录到本地数据库可以看到相关的权限：

SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');

可以看出，在数据库中 DBLINK 有3种权限：

1. CREATE DATABASE LINK：所创建的 DBLINK 只能是创建者自己使用，别的用户不能使用。
2. CREATE PUBLIC DATABASE LINK：PUBLIC 表示所创建的 DBLINK 所有用户都可以使用。
3. DROP PUBLIC DATABASE LINK：删除公共的DBLINK。

可以使用如下授权语句给用户 SCOTT 授予 CREATE PUBLIC DATABASE LINK 和 DROP PUBLIC DATABASE LINK 两个权限：

GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK TO SCOTT;

查询数据库的 DBLINK 可以使用SQL语句：

SELECT * FROM DBA_DB_LINKS;

1.1 创建 DBLINK

方法一：

create database link link_ora_mydb 
	connect to system identified by pwd123 
	using '127.0.0.1:1521/MYDB';
	
-- 如果两个 oracle 数据库在同一台服务器，则还可以如下创建
create database link link_ora_mydb2 
	connect to system identified by pwd123 
	using 'MYDB';

说明：

• link_ora_mydb 是远程数据库的别名；
• system 是远程数据库用户名；
• pwd123 是密码；
• MYDB 是远程 oracle 实例名。

方法二：
第二种方式，是在本地数据库 tnsnames.ora 文件中没有配置要访问的远程数据库的时候，而直接将相关的内容写到 DBLINK 的配置中，如下所示：

create database link 随便起个名字
  connect to 用户名 identified by 密码
  using '(DESCRIPTION =                       
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 域名)(PORT = 端口号))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 数据库实例名称)
    )
  )';

1.2 查询远程数据库数据

查询远程数据库表时使用 远程数据库表@远程数据库别名/dblink 名。

-- 在本地数据库查询远程数据库的表数据
select a.* from a_cls2@link_ora_mydb a;

-- 在本地数据库查询，将本地数据库表与远程数据库表关联查询
select a.*,b.cls from a_user a,a_cls2@link_ora_mydb b where a.cid = b.id;

参考文章：
http://blog.itpub.net/26736162/viewspace-2148696/