Oracle dblink的说明和简单使用

 

 在跨数据库查询的时候时常会用到dblink，例如：两台不同的数据库服务器，从一台数据库服务器的一个用户读取另一台数据库服务器下面的某个schema的数据，这个时候，使用dblink能够很方便的实现。
dblink和数据库中的view功能类似，建立dblink的时候需要直接到待读取数据库的ip地址，ssid以及数据库的用户名和密码。

第一部分：如何创建dblink
dblink的创建有两种方式：
1、在本地数据库tnsnames.ora文件中配置要访问的目标数据库信息
create [public] database link to_mai
connect to scott identified by tiger using 'to_mai';
其中第一个to_mai是将要创建的dblink的名字，
第二个to_mai是tnsnames.ora文件中配置的目标数据库mai的连接信息：
to_mai =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.123)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mai)
    )
  )
scott/tiger是登录到远程数据库的用户名和密码，然后当在本地数据库使用to_mai的dblink访问远程数据库的时候，会使用scott的身份来访问
2、本地数据库tnsnames.ora文件中没有配置要访问的数据库信息
create [public] database link to_mai
connect to scott identified by tiger
using
  '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.123)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mai)
    )
  )';
这一种方式其实是把第一种配置在tnsnames.ora文件中的信息，直接放在dblink的后面

要查询对方数据库的表TableName语句如下:
SELECT 字段名 FROM TableName@数据链名称;

第二部分：dblink相关的权限
SYS@test > select * from user_sys_privs where privilege like upper('%link%');
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SYS                            CREATE DATABASE LINK                     NO
SYS                            DROP PUBLIC DATABASE LINK                NO
SYS                            CREATE PUBLIC DATABASE LINK              NO
权限解释：
CREATE DATABASE LINK(所创建的DBlink只能是创建者自己使用，别的用户都使用不了)
CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用)
DROP PUBLIC DATABASE LINK(删除Public dblink的权限)

第三部分：dblink的使用
假如说，我要比较两个库的用户的差异，那么我现在可以这么做：
select username from dba_users@to_mai

minus
select username from dba_users;
这样就可以得到在目标数据库中有的用户，而在当前用户中没有的用户列表了
另外一个：如果你乐意，你可以用triggers+dblink实现一个简单的数据同步，不过不建议使用，玩玩就行了。

第四部分：dblink的相关视图

dblink的常用视图有如下几个：
dba_db_links，查看系统中所有的dblink
v$dblink，用来查看当前数据库中有哪些打开的dblink

 

select * from user_db_links; --用户 DB Link 

select * from dba_db_links;  --dba DB Link 

select * from v$dblink;      --当前DB Link

Drop database link MYDBLINK1;

 

如果是相同实例，不同用户，可以用如下方法查询

select * from 用户名.表名