Oracle DBLink兼容性增强

可获得性

本特性自MogDB 5.0.0版本开始引入。

特性简介

DBLink全称为database link，是一种数据库之间的单向连接，通常用来创建外部数据库的连接并对外部数据进行DML操作。本特性支持Oracle DBLink语法，通过oracle_fdw插件可直接使用@符号在MogDB数据库中访问Oracle数据库中的表。

客户价值

增强MogDB与Oracle的兼容性，减少应用程序的迁移代价。

特性描述

本特性支持Oracle DBLink语法，可以使用@符号访问Oracle数据库中的表。

支持通过DBLink执行SELECT、INSERT、UPDATE、DELETE、EXPLAIN操作。

特性约束

• 仅支持 SELECT、INSERT、UPDATE、DELETE、EXPLAIN 操作。

语法描述

• 通过DBLink连接到Oracle数据库

  create server <dblink_name> foreign data wrapper oracle_fdw options(dbserver '<IP:PORT>/db_name'); 

  • dblink_name：表示自定义的DBLink名称。
  • IP:PORT：表示Oracle数据库所在的服务器IP地址和端口号。
  • db_name：表示Oracle数据库名。

• 通过DBLink创建用户映射。

  create user mapping for <mogdb_user_name> server <dblink_name> options(user '<oracle_user_name>',password 'oracle_password');

  • mogdb_user_name：表示MogDB数据库用户名。
  • dblink_name：表示自定义的DBLink名称。
  • oracle_user_name：表示Oracle数据库的用户名。
  • oracle_password：表示Oracle数据库的用户名密码。

• 通过DBLink查询表

  SELECT * FROM <oracle_table_name>@<dblink_name>;

• 通过DBLink插入数据

  INSERT INTO <oracle_table_name>@<dblink_name> VALUES (...);

• 通过DBLink更新表数据

  UPDATE <oracle_table_name>@<dblink_name> SET... WHERE...;

• 通过DBLink删除表数据

  DELETE FROM <oracle_table_name>@<dblink_name> where ...;

• 查看执行计划

  explain SELECT * FROM <oracle_table_name>@<dblink_name>;

说明：

• oracle_table_name：表示Oracle数据库的表名。
• dblink_name：表示自定义的DBLink名称。

示例

环境准备

MogDB 环境

• 已安装MogDB数据库。
• 已安装oracle_fdw插件。安装方法参见oracle_fdw。

Oracle 环境

1. 已登录数据库。

2. 新建表。

   CREATE TABLE scott.EMPLOYEE (
   ID INT PRIMARY KEY,
   NAME VARCHAR2(50) NOT NULL,
   SALARY NUMBER(10,2)
   );

3. 插入测试数据。

   INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1001, 'Mike', 5000);
   INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1002, 'JACK', 6000);

步骤

1. 登录MogDB数据库，以数据库postgres，端口号27000为例。

   [omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r
   gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   Type "help" for help.
   
   MogDB=#

2. 创建用户，赋予用户sysadmin权限，以用户名mymogdb50为例。

   MogDB=# create user mymogdb50 identified by 'Enmo@123';
   NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
   CREATE ROLE
   MogDB=# alter user mymogdb50 sysadmin;
   ALTER ROLE

3. 退出数据库。

   MogDB=# \q
   [omm5@localhost oracle_file]$

4. 为用户创建用户映射密钥文件。

   [omm5@localhost oracle_file]$ gs_guc generate -S 'xxxx@123' -D $GAUSSHOME/bin -o usermapping -U mymogdb50
   The gs_guc run with the following arguments: [gs_guc -S ******** -D /data/mogdb500/app/bin -o usermapping -U mymogdb50 generate ].
   gs_guc generate -S *** -U ***

   说明：-S 表示自定义密钥，例如xxxx@123。

5. 以mymogdb50用户登录MogDB数据库。

   [omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r -U mymogdb50 -W 'Enmo@123'
   gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   Type "help" for help.
   
   MogDB=>

6. 创建MogDB数据库到Oracle数据库的DBLink连接，以IP地址127.2.15.23，端口号55446，数据库名pdb_test 为例。

   MogDB=> CREATE SERVER db_link_to_pdb_test_15_2 FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver '121.36.15.2:55446/pdb_test');
   CREATE SERVER

7. 创建用户映射。为MogDB数据库用户mymogdb50和Oracle数据库用户scott建立映射关系。

   MogDB=> create user mapping for mymogdb50 server db_link_to_pdb_test_15_2 options(user 'scott',password 'xxx123');
   CREATE USER MAPPING

   说明：用户名scott以及密码xxx123需要根据实际环境进行替换。

8. 通过DBLink在MogDB数据库中查询Oracle表。

   MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2;
     id  | name | salary
   ------+------+---------
    1001 | Mike | 5000.00
    1002 | JACK | 6000.00
   (2 rows)
   
   MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY > 400;
     id  | name | salary
   ------+------+---------
    1001 | Mike | 5000.00
    1002 | JACK | 6000.00
   (2 rows)

9. 通过DBLink在MogDB数据库中对Oracle数据库表执行更新、插入、删除、查询操作，以scott.EMPLOYEE表为例。

   MogDB=> UPDATE scott.EMPLOYEE@db_link_to_pdb_test_15_2 SET SALARY = 5500 WHERE ID = 1001;
   UPDATE 1
   
   MogDB=> insert into scott.EMPLOYEE@db_link_to_pdb_test_15_2 values (1003, 'JANE', 7000);
   INSERT 0 1
   
   MogDB=> DELETE FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY =6000;
   DELETE 1
   
   MogDB=> select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
     id  | name | salary
   ------+------+---------
    1003 | JANE | 7000.00
    1001 | MIKE | 5500.00
   (2 rows)

10. 通过DBLink在MogDB数据库中对Oracle数据库表执行查看执行计划操作。

    MogDB=> explain select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
                                                        QUERY PLAN
    
    -----------------------------------------------------------------------------------------------
    -------------------
     Foreign Scan on "scott.employee@db_link_to_pdb_test_15_2" employee  (cost=10000.00..20000.00 r
    ows=1000 width=78)
       Oracle query: SELECT /*66abc20a4a7895b75898e391381f9de8*/ r1."ID",r1."NAME",r1."SALARY" FROM
     scott.employee r1
    (2 rows)

相关页面

oracle_fdw