Oracle-跨库访问DBLINK

1. DBLINK的先容

Oracle在进行跨库访问时，可以创建DBLINK实现，比如要将UAT的表数据灌入开辟情况，则可以使用UAT库为数据源，通过DBLINK实现将查出的数据灌入开辟库。
简而言之就是在当前数据库中访问另一个数据库中的表中的数据

---

2. DBLINK的使用简介

2.1 权限问题

要想创建DBLINK，首先要检查当前用户是否具备权限。

1. SELECT * FROM user_sys_privs WHERE PRIVILEGE LIKE UPPER('%DATABASE LINK%');

复制代码

   CREATE PUBLIC DATABASE LINK：表示所创建的dblink全部用户都可以使用

CREATE DATABASE LINK：表示所创建的dblink只能是创建者能使用，别的用户不可以使用

DROP PUBLIC DATABASE LINK ： 表示所创建的dblink所用用户都可以删除
  如果没有以上权限，但是有UNLIMITED TABLESPACE权限，也可以实现DBLINK的创建和删除(实测)。

1. SELECT * FROM user_sys_privs WHERE PRIVILEGE LIKE UPPER('%UNLIMITED TABLESPACE%');

复制代码

如果指定用户没有上面的几个权限，必要通过sys用户（这个充当管理员角色，有很大权限）赋予权限，你必要登陆sys用户，并在sys用户下实行以下语句：

1. GRANT CREATE PUBLIC DATABASE LINK , DROP PUBLIC DATABASE LINK TO hxapp;

复制代码

  用来登录到远程数据库的帐号必须有CREATE SESSION权限
  2.2 创建DBLINK

创建时加不加PUBLIC关键字，会影响DBLINK的使用范围，加上PUBLIC关键字则全部用户都可以使用，否则只有当前用户大概更高权限的用户可以使用。

• 通过pl/sql developer图形化界面创建
  
  
  
  
  等价于
  1. CREATE DATABASE LINK DBLINK_TEST_TO230
  2.   CONNECT TO hxapp IDENTIFIED BY hxapp
  3.   USING '3.1.11.230/cbsznckdb';

  复制代码
• 如果网络配置文件tnsnames.ora配置了连接信息，则可以使用如下语句实现：
  1. CREATE DATABASE LINK &dblink_name
  2.         CONNECT TO &db_name IDENTIFIED BY &db_passwd
  3.         USING '&db_sid';

  复制代码
  
  
  
• 如果网络配置文件tnsnames.ora未配置连接信息，则可以使用如下语句实现：
  

1. CREATE DATABASE LINK &dblink_name
2. CONNECT TO &db_name IDENTIFIED BY &db_passwd
3. USING '(DESCRIPTION =
4.          (ADDRESS_LIST =
5.            (ADDRESS =
6.              (PROTOCOL = TCP)
7.              (HOST = &ip)
8.              (PORT = 1521)
9.            )
10.          )
11.          (CONNECT_DATA = (SERVICE_NAME = &sid)
12.          )
13.        )';
14. --例：
15. CREATE DATABASE LINK &dblink_name
16. CONNECT TO &db_name IDENTIFIED BY &db_password
17. USING '(DESCRIPTION =
18.          (ADDRESS_LIST =
19.            (ADDRESS =
20.              (PROTOCOL = TCP)
21.              (HOST = 3.1.11.230)
22.              (PORT = 1521)
23.            )
24.          )
25.          (CONNECT_DATA = (SERVICE_NAME = cbsznckdb)
26.          )
27.        )';
28. --简写
29.         CREATE DATABASE LINK DBLINK_TEST_01
30.           CONNECT TO hxapp IDENTIFIED BY hxapp
31.           USING '3.1.11.230/cbsznckdb';

复制代码

创建以后查询效果：

2.3 查询DBLINK

• 查询当前实例下全部的DBLINK
  

1. SELECT * FROM dba_db_links t;

复制代码

• 查询当前用户下全部的DBLINK
  

1. SELECT * FROM user_db_links t;

复制代码

2.4 删除DBLINK

• 如果创建的是一个public的DBLINK，删除时必要用
  

1. DROP PUBLIC DATABASE LINK &dblink_name;

复制代码

• 如果创建的是一个private的DBLINK，删除时必要用
  

1. DROP DATABASE LINK &dblink_name;

复制代码

3. DBLINK实际应用语句举例

1. --插入语句
2. INSERT INTO test_01@DBLINK_TEST_01 t
3.   SELECT 'UntifA' FROM dual;
4. --查询语句
5. SELECT * FROM test_01@DBLINK_TEST_01 t WHERE t.name = 'UntifA';
6. --删除语句
7. DELETE FROM test_01@DBLINK_TEST_01 t WHERE t.name = 'UntifA';
8. --更新语句
9. UPDATE test_01@DBLINK_TEST_01 t
10.    SET t.name = 'mengkiD~lufy'
11. WHERE t.name = 'UntifA';

复制代码