很惊喜的发现YashanDB个人版支持DBLink功能，熟悉Oracle的朋友对这个功能应该不陌生。Database Link即数据库链接，通过在本地数据库创建一个指向远端数据库的link，用户可以像访问本地数据库一样访问远端数据库的数据，实现了跨数据库的访问。从崖山的文档可知，当前崖山数据库支持YashanDB->YashanDB、YashanDB->Oracle和Oracle->YashanDB的数据库link，下面我们就来尝试使用看看。
崖山数据库到崖山数据的DBLink
管理DBLink
支持DDL，包括create database link，alter database link，drop database link，详细的语法可以参考官方文档，下面实际操作看看：
SQL> create database link test_link;
Succeed.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME PASSWORD CREDENTIAL_NAME CREDENTIAL_OWNER HOST CREATED HIDDEN SHARD_INTERNAL VALID INTRA_CDB
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- ------ -------------- ----- ---------
SYS TEST_LINK 2023-12-03 NO NO YES NO
1 row fetched.
SQL> alter database link test_link;
Succeed.
SQL> select * from dual@test_link;
YAS-07318 failed to call external module
YAS-00045 illegal data format
SQL> drop database link test_link;
Succeed.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME PASSWORD CREDENTIAL_NAME CREDENTIAL_OWNER HOST CREATED HIDDEN SHARD_INTERNAL VALID INTRA_CDB
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- ------ -------------- ----- ---------
0 rows fetched.如上，我们可以从dba_db_links视图中查询到创建的DBLink信息，上面创建的link没有指定远端数据库的信息，实际使用时（select * from dual@test_link）报错了。
使用DBLink
要想真正的访问远端数据库，创建DBLink时需要指定远端数据库的信息，包括用户名密码，数据库的IP端口等，这里我们可以安装2个崖山数据库，其中一个作为远端（亲测，可以安装在同一个设备不同用户下，指定不同的端口即可），然后使用如下命令创建和使用DBLink：
SQL> create database link dblink_y2y connect to sys identified by Cod-2022 using '192.168.111.137:1788';
Succeed.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME PASSWORD CREDENTIAL_NAME CREDENTIAL_OWNER HOST CREATED HIDDEN SHARD_INTERNAL VALID INTRA_CDB
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------- ------ -------------- ----- ---------
SYS DBLINK_Y2Y SYS 099D3BDC080000009806AA9778B1E3416CDEF69CE344F44159E06956B1A3F24111FA5E232948EF41 192.168.111.137:1788 2023-12-03 NO NO YES NO
1 row fetched.
SQL> select * from dual@dblink_y2y;
DUMMY
-----
X
1 row fetched.成功访问了远端的dual，我们尝试在远端创建一个表，进行一些操作看看呢，看下支持哪些操作，从官方文档上看，没有相关的汇总介绍，哪些语句支持DBLink是散落在各语句的介绍中，需要吐槽下，建议后面能够完善。
1、首先在远端崖山数据库创建一个表并插入数据
SQL> create table tbl_test_dblink(c_sint smallint, c_int int, c_integer integer, c_float float, c_num1 number(38),
c_decimal decimal(5,2),c_date date, c_timestamp timestamp, c_interval_ym interval year(4) to month,
c_interval_ds interval day(9) to second(9), c_char char(20), c_varchar varchar(30), c_varchar2 varchar2(40),
c_raw raw(100), c_bfloat binary_float, c_bdouble binary_double); 2 3 4
Succeed.
SQL> insert into tbl_test_dblink values(1001, 1004, 300, 3.14, 654321, 123.12, to_date('2020-11-01', 'yyyy-mm-dd'),
to_timestamp('2020-01-01 23:59:59.999999', 'yyyy-mm-dd hh24:mi:ss.ff'), '1000-0', '50 10:30:59.999999', 'beijin',
'varchar_1001', 'varchar2_len_40', '123', -31.1234567, 31.1234567890123456); 2 3
1 row affected.
SQL> commit;
Succeed.
SQL>2、在本地崖山进行查询操作，成功
SQL> select * from tbl_test_dblink@dblink_y2y;
C_SINT C_INT C_INTEGER C_FLOAT C_NUM1 C_DECIMAL C_DATE C_TIMESTAMP C_INTERVAL_YM C_INTERVAL_DS C_CHAR C_VARCHAR C_VARCHAR2 C_RAW C_BFLOAT C_BDOUBLE
-------- ------------ ------------ ----------- ----------- ----------- -------------------------------- ---------------------------------------------------------------- --------------- -------------------------------- --------------------- --------------------------------- ----------------------------------------- ---------------------------------------------------------------- ----------- -----------
1001 1004 300 3.14E+000 654321 123.12 2020-11-01 2020-01-01 23:59:59.999999 +1000-00 +50 10:30:59.999999 beijin varchar_1001 varchar2_len_40 0123 -3.11E+001 3.112E+001
1 row fetched.
SQL> select * from tbl_test_dblink@dblink_y2y where c_sint=1001;
C_SINT C_INT C_INTEGER C_FLOAT C_NUM1 C_DECIMAL C_DATE C_TIMESTAMP C_INTERVAL_YM C_INTERVAL_DS C_CHAR C_VARCHAR C_VARCHAR2 C_RAW C_BFLOAT C_BDOUBLE
-------- ------------ ------------ ----------- ----------- ----------- -------------------------------- ---------------------------------------------------------------- --------------- -------------------------------- --------------------- --------------------------------- ----------------------------------------- ---------------------------------------------------------------- ----------- -----------
1001 1004 300 3.14E+000 654321 123.12 2020-11-01 2020-01-01 23:59:59.999999 +1000-00 +50 10:30:59.999999 beijin varchar_1001 varchar2_len_40 0123 -3.11E+001 3.112E+001
1 row fetched.
SQL>3、进行DML操作，也都成功啦，看来功能完备的还是不错的。
SQL> insert into tbl_test_dblink@dblink_y2y (c_sint) values(1002);
1 row affected.
SQL> select * from tbl_test_dblink@dblink_y2y;
C_SINT C_INT C_INTEGER C_FLOAT C_NUM1 C_DECIMAL C_DATE C_TIMESTAMP C_INTERVAL_YM C_INTERVAL_DS C_CHAR C_VARCHAR C_VARCHAR2 C_RAW C_BFLOAT C_BDOUBLE
-------- ------------ ------------ ----------- ----------- ----------- -------------------------------- ---------------------------------------------------------------- --------------- -------------------------------- --------------------- --------------------------------- ----------------------------------------- ---------------------------------------------------------------- ----------- -----------
1001 1004 300 3.14E+000 654321 123.12 2020-11-01 2020-01-01 23:59:59.999999 +1000-00 +50 10:30:59.999999 beijin varchar_1001 varchar2_len_40 0123 -3.11E+001 3.112E+001
1002
2 rows fetched.
SQL> update tbl_test_dblink@dblink_y2y set C_INT=2000 where c_sint=1002;
1 row affected.
SQL> select * from tbl_test_dblink@dblink_y2y where c_sint=1002;
C_SINT C_INT C_INTEGER C_FLOAT C_NUM1 C_DECIMAL C_DATE C_TIMESTAMP C_INTERVAL_YM C_INTERVAL_DS C_CHAR C_VARCHAR C_VARCHAR2 C_RAW C_BFLOAT C_BDOUBLE
-------- ------------ ------------ ----------- ----------- ----------- -------------------------------- ---------------------------------------------------------------- --------------- -------------------------------- --------------------- --------------------------------- ----------------------------------------- ---------------------------------------------------------------- ----------- -----------
1002 2000
1 row fetched.
SQL> delete from tbl_test_dblink@dblink_y2y where c_sint=1001;
1 row affected.
SQL> select * from tbl_test_dblink@dblink_y2y;
C_SINT C_INT C_INTEGER C_FLOAT C_NUM1 C_DECIMAL C_DATE C_TIMESTAMP C_INTERVAL_YM C_INTERVAL_DS C_CHAR C_VARCHAR C_VARCHAR2 C_RAW C_BFLOAT C_BDOUBLE
-------- ------------ ------------ ----------- ----------- ----------- -------------------------------- ---------------------------------------------------------------- --------------- -------------------------------- --------------------- --------------------------------- ----------------------------------------- ---------------------------------------------------------------- ----------- -----------
1002 2000
1 row fetched.
SQL>
4、目前不支持对远端表进行DDL操作
SQL> drop table tbl_test_dblink@dblink_y2y;
[1:27]YAS-04916 operations on dblink table are not allowed
SQL> create table tbl_test_dblink2@dblink_y2y(c_sint int);
[1:30]YAS-04916 operations on dblink table are not allowed崖山数据库到Oracle的DBLink
类似操作，不过遇到点麻烦：
SQL> create database link dblink_y2o connect to system identified by oracle using 'oracle:192.168.111.137:1521/oracledb';
Succeed.
SQL> select * from dual@dblink_y2o;
YAS-07318 failed to call external module
YAS-00509 failed to load dynamic library libdrv_oracle.so, libclntsh.so.21.1: cannot open shared object file: No such file or directory缺少相关的库，这块到网上查了下，需要下载oracle的instantclient，下载地址是：
https://www.oracle.com/cn/database/technologies/instant-client/linux-x86-64-downloads.html
然后把库解压后全部copy放到崖山的安装目录lib下即可，然后再次执行，成功啦：
SQL> select * from dual@dblink_y2o;
DUMMY
-----
X
1 row fetched.今天就体验到这里，DBLink功能强大，还有很多值得探究，从上面简单的体验可以看出，崖山数据库在DBLink功能上完成的非常高！
最后修改时间：2023-12-04 09:51:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
