DBLink

📄字数 4.6K

👁️阅读量加载中...

数据库链接（DBLink）是一种特殊的数据库实例对象，定义了一个数据库访问另一个数据库的外部链接，允许访问其他同构或异构数据库的对象，从而实现跨数据源访问和操作能力。

DBLink支持ODBC和XGCI两种连接方式链接远端数据库，推荐使用ODBC方式链接。

ODBC：适用于链接符合MS ODBC 3.X标准的数据库，如虚谷数据库、Oracle、MySQL、PostgreSQL等。使用前需正确配置ODBC环境，参考 ODBC开发手册。
XGCI：适用于链接另一个虚谷数据库。XGCI详情参考XGCI开发手册。

一、创建DBLink

创建一个访问指定远端数据库的DBLink，需要指定DBLink名称并且提供远端数据库的用户名、密码、IP 地址、端口号以及访问类型。

注意

不支持链接自身数据库实例。

1.1 语法格式

1.2 参数说明

CreateDBLinkODBCStmt: ODBC方式连接，CreateDBLinkXGCIStmt：XGCI方式连接。
PUBLIC：此链接对象是否能被创建者之外的用户使用，缺省则为私有。
link_name：指定DBLink的名称。
FOR db_type：连接方式。
- db_type：数据库类型。通过系统表SYS_DBLINK_SP_TYPES查看已支持的数据库类型。
dblink_uri：指定远程连接URI，格式为db_name@ip:port。
usr_name：连接远程数据库的用户名。
password：连接远程数据库用户的口令。
dblink_odbcinst：链接的数据源名称，即odbc.ini文件中配置的数据源。

提示

ODBC方式连接：需正确配置odbcinst字段，配置为本端环境中ODBC配置odbc.ini中的数据源名称。connect_uri字段仅用于记录远端的URI信息，可缺省。
XGCI方式连接：需正确配置connect_uri字段。

1.3 示例

示例1

使用ODBC方式，创建一个连接到其他服务器上的虚谷数据库的外部链接，参数如下所示：

远端数据库名：DB_LINK_TEST。
IP地址：192.168.2.238。
端口：5186。
登录到远端的用户名：SYSDBA。
登录口令：SYSDBA。
数据源：xugu_238_5186，对应odbc.ini中的数据源名称。

sql

SQL> CREATE PUBLIC DATABASE link link_xg FOR OXUGU CONNECT TO 'DB_LINK_TEST@192.168.2.238:5186' USER 'SYSDBA' IDENTIFIED BY 'SYSDBA' USING 'xugu_238_5186';

示例2

使用ODBC方式，创建一个外部链接 link_orcl，它可以连接到其他服务器上的Oracle数据库，参数如下所示：

IP地址：10.28.25.100。
端口：1521。
数据库名称：free。
登录到远端的用户名：oracle。
登录口令：oracle。
数据源名称：oracle_100。

sql

SQL> CREATE PUBLIC DATABASE LINK link_orcl FOR ORACLE CONNECT TO 'free@10.28.25.100:1521' USER 'oracle' IDENTIFIED BY 'oracle' USING 'oracle_100';

示例3

使用XGCI方式，创建一个连接到其他服务器上的虚谷数据库的外部链接，参数如下所示：

远端数据库名：DB_LINK_TEST。
IP地址：192.168.2.238。
端口：5186。
登录到远端的用户名：SYSDBA。
登录口令：SYSDBA。

sql

SQL> CREATE PUBLIC DATABASE link link_xgci CONNECT TO 'DB_LINK_TEST@192.168.2.238:5186' USER 'SYSDBA' IDENTIFIED BY 'SYSDBA';
-- 或使用数据库类型XUGU
SQL> CREATE PUBLIC DATABASE link link_xgci FOR XUGU CONNECT TO 'DB_LINK_TEST@192.168.2.238:5186' USER 'SYSDBA' IDENTIFIED BY 'SYSDBA' USING 'xugu_238_5186';

二、查看DBLink

2.1 查看已创建的DBLink

创建DBLink后，可以通过 DBA_DBLINKS 系统视图查看已创建的DBLink信息。

示例

查看已创建的DBLink信息。

sql

 SQL> SELECT * FROM DBA_DBLINKS;

+-------+---------+---------+-----------+--------------+------+----------+-----------+-------------+--------+-----------+----------+
| DB_ID | DBLK_ID | DB_TYPE | DBLK_NAME |      IP      | PORT | RDB_NAME | USER_NAME | CONNECT_STR | VALID  | IS_PUBLIC | OWNER_ID |
+-------+---------+---------+-----------+--------------+------+----------+-----------+-------------+--------+-----------+----------+
| 2     | 1048576 | 3       | LINK_ORCL | 10.28.25.100 | 1521 | free     | oracle    | oracle_100  | <NULL> | T         | 0        |
+-------+---------+---------+-----------+--------------+------+----------+-----------+-------------+--------+-----------+----------+

2.2 查看远端数据库类型DB_TYPE

通过系统表SYS_DBLINK_SP_TYPES查看远端数据库类型DB_TYPE。

示例

sql

SQL> SELECT * FROM SYS_DBLINK_SP_TYPES;

+---------+------------+------------+---------------+--------------+
| TYPE_ID |    NAME    | ALIAS_NAME | FIRST_VERSION | LAST_VERSION |
+---------+------------+------------+---------------+--------------+
| 1       | XuGu       | <NULL>     | <NULL>        | <NULL>       |
| 2       | OXuGu      | <NULL>     | <NULL>        | <NULL>       |
| 3       | Oracle     | <NULL>     | <NULL>        | <NULL>       |
| 4       | SQLServer  | MSSQL      | <NULL>        | <NULL>       |
| 5       | MySQL      | <NULL>     | <NULL>        | <NULL>       |
| 6       | DB2        | <NULL>     | <NULL>        | <NULL>       |
| 7       | PostgreSQL | <NULL>     | <NULL>        | <NULL>       |
| 8       | SQLITE     | <NULL>     | <NULL>        | <NULL>       |
| 9       | MARIADB    | <NULL>     | <NULL>        | <NULL>       |
+---------+------------+------------+---------------+--------------+

三、使用DBLink

3.1 通过DBLink访问远端数据库中的数据

通过DBLink访问远端数据库的对象，对象包括表、视图、同义词、序列、索引、约束、子查询等。

访问远端数据库的对象，使用方式是在对象的后面，用@符号后接DBLink名。

示例

在Oracle数据源 oracle_100 下有建表，插入数据：

sql

SQL> CREATE TABLE migrate_char(c1 NUMBER(10),c2 VARCHAR2(12));
SQL> INSERT INTO migrate_char VALUES(1,'一二三四');
SQL> INSERT INTO migrate_char VALUES(2,'abcdef');

创建外部链接 link_orcl，查询远程表 migrate_char：

sql

SQL> CREATE PUBLIC DATABASE LINK link_orcl FOR ORACLE CONNECT TO 'free@10.28.25.100:1521' USER 'oracle' IDENTIFIED BY 'oracle' USING 'oracle_100';
SQL> SELECT * FROM migrate_char@link_orcl;

+----+----------+
| C1 |    C2    |
+----+----------+
| 1  | 一二三四 |
| 2  | abcdef   |
+----+----------+

3.2 通过DBLink修改远端数据库中的数据

通过DBLink修改远端数据库中数据的操作，包括插入数据、更新数据和删除数据等操作，主要通过 INSERT、UPDATE 和 DELETE 三种 DML 语句来实现。

示例

向远程表 migrate_char，插入数据、更新数据和删除数据。

sql

SQL> INSERT INTO migrate_char@link_orcl VALUES(3,'七八九');
SQL> UPDATE migrate_char@link_orcl SET C2 = 'xyz' WHERE C1 = 2;
SQL> DELETE FROM migrate_char@link_orcl WHERE C1 = 2;

创建本地表 tab_char，插入数据。

sql

SQL> CREATE TABLE tab_char(c1 number(10),c2 varchar2(12));
SQL> INSERT INTO tab_char values(1,'一二三五');
SQL> INSERT INTO tab_char values(5,'qazwsx');

可以查询本地表或其他链接的表对远程表进行操作，如：

使用INSERT INTO SELECT 语句，复制本地表 tab_char 信息到远程表 migrate_char@link_orcl。

sql

SQL> INSERT INTO migrate_char@link_orcl SELECT c1+100,c2 FROM tab_char;

本地表和远程表关联查询。

sql

SQL> SELECT a.c1 a_c1,a.c2 a_c2 ,b.c1 b_c1,b.c2 b_c2 FROM migrate_char@link_orcl a LEFT JOIN TAB_CHAR b ON a.c1 = b.c1;

+------+----------+--------+----------+
| A_C1 |   A_C2   |  B_C1  |   B_C2   |
+------+----------+--------+----------+
| 1    | 一二三四 | 1      | 一二三五 |
| 3    | 七八九   | <NULL> | <NULL>   |
| 101  | 一二三五 | <NULL> | <NULL>   |
| 105  | qazwsx   | <NULL> | <NULL>   |
+------+----------+--------+----------+

四、删除DBLink

目前不支持修改DBLink的连接信息，如IP、端口、用户或口令。如果连接信息更改，可删除DBLink，重新创建。

注意

删除不存在的链接会返回错误。

4.1 语法格式

4.2 参数说明

dblink_name：待操作的数据库链接名称。

4.3 示例

删除数据库链接 link_orcl。

sql

DROP DATABASE LINK link_orcl;

五、权限说明

创建DBLink的用户需具备CREATE ANY DATABASE LINK权限，删除DBLink的用户需要具备DROP ANY DATABASE LINK权限。权限详情参考权限管理。

创建DBLink时，指定PUBLIC，则创建对所有用户可见的公共数据库链接。如果省略，则创建私有数据库链接。

公共数据库链接：具有DBLink创建权限的用户，创建DBLink实例时，指定PUBLIC关键字，创建的DBLink实例为公有数据库链接，允许其所属数据库的所有用户访问。
私有数据库链接：具有DBLink创建权限的用户，创建DBLink实例时，缺省PUBLIC关键字，创建的DBLink实例为私有数据库链接，只允许创建DBLink的用户访问。

用户权限和公私有DBLink的关系说明：

用户权限	公有DBLink	私有DBLink
普通无权限用户	不支持操作	不支持操作
仅有创建私有DBLink权限	不支持操作	支持创建和删除
仅有创建公有DBLink权限	支持创建，不支持删除	不支持操作
同时有创建私有公有DBLink权限	支持创建，不支持删除	支持创建和删除
仅有删除公有DBLink权限	不支持创建，支持删除	不支持操作

六、使用说明

使用DBLink对远端数据库进行访问，有以下使用说明和注意事项。

使用ODBC方式时，需正确配置数据源的ODBC驱动。
尽量使用ODBC标准包含的数据类型，并验证类型是否能正确映射。如果无法正确映射，则将抛出异常，终止当前命令。
支持复杂SQL中多个远程表的操作，如TPCH的24条语句。
支持虚谷数据库数据类型的查询与DML操作。
支持Prepare语句对DBLink查询进行预处理。
不支持对DBLink表进行DDL操作，如DROP TABLE，ALTER TABLE等操作。
不支持分区查询。
DML不支持Prepare参数化执行。
虚谷数据库本地没有对远程表进行缓存的功能。

DBLink ​

一、创建DBLink ​

1.1 语法格式 ​

1.2 参数说明 ​

1.3 示例 ​

示例1 ​

示例2 ​

示例3 ​

二、查看DBLink ​

2.1 查看已创建的DBLink ​

2.2 查看远端数据库类型DB_TYPE ​

三、使用DBLink ​

3.1 通过DBLink访问远端数据库中的数据 ​

3.2 通过DBLink修改远端数据库中的数据 ​

示例 ​

四、删除DBLink ​

4.1 语法格式 ​

4.2 参数说明 ​

4.3 示例 ​

五、权限说明 ​

六、使用说明 ​