KingbaseES V8R6运维案例之---Oracle_fdw访问外部表和视图

案例说明：
在KingbaseES数据库通过oracle_fdw插件访问Oracle数据库表和视图。

适用版本：
KingbaseES V8R6

**一、oracle_fdw简介 **

 oracle_fdw是KingbaseES的一个扩展插件，它提供了一个外部数据包装器，可以方便高效的访问oracle数据库，包括WHERE条件和所需列的下推以及全面的EXPLAIN支持。

二、Oracle端配置
1、Oracle用户和对象
用户scott/tiger,如下所示拥有对象：

10:51:09 SCOTT@prod> select * from tab

TNAME                          TABTYPE        CLUSTERID
------------------------------ ------------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

2、启动listener

[oracle@node202 ~]$ lsnrctl status
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 01-DEC-2023 10:56:22
Copyright (c) 1991, 2021, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                01-DEC-2023 10:49:35
Uptime                    0 days 0 hr. 6 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /home/app/oracle/diag/tnslsnr/node202/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node202)(PORT=1521)))
Services Summary...
Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

三、KingbaseES端配置

1、创建server

prod=# create server to_ora foreign data wrapper oracle_fdw options(dbserver '//192.168.1.202:1521/prod');
CREATE SERVER

prod=# grant usage on foreign server to_ora to tom;
GRANT

2、建立user_map

prod=# create user mapping for tom server to_ora options(user 'scott',password 'tiger');
CREATE USER MAPPING

3、查看server和user_map信息

prod=# select * from pg_foreign_server where srvname='to_ora';
  oid  | srvname | srvowner | srvfdw | srvtype | srvversion |             srvacl             |              srvo
ptions
-------+---------+----------+--------+---------+------------+--------------------------------+------------------
--------------------
 24585 | to_ora  |       10 |  24584 |         |            | {system=U/system,tom=U/system} | {dbserver=//192.1
68.1.202:1521/prod}
(1 row)

prod=#  select * from pg_user_mappings where srvname='to_ora';
 umid  | srvid | srvname | umuser | usename |                             umoptions
-------+-------+---------+--------+---------+-------------------------------------------------------------------
 24586 | 24585 | to_ora  |  24576 | tom     | {user=scott,password=j7BNva/xI3M13arc43Kx6A==,user_pwd_encrypt=1}
(1 row)

4、创建外部表

prod=#create foreign table ft_emp(empno integer,ename varchar(10)) server to_ora options(schema 'scott', table 'emp');
CREATE FOREIGN TABLE
prod=> select * from ft_emp;
ERROR:  Oracle table "scott"."emp" for foreign table "ft_emp" does not exist or does not allow read access
DETAIL:  ORA-00942: table or view does not exist
HINT:  Oracle table names are case sensitive (normally all uppercase).

如下图所示，用户名和表名称小写导致错误：

tom用户创建：

prod=> create foreign table ft_emp(empno integer,ename varchar(10))
prod-> server to_ora options(schema 'SCOTT' , table 'EMP');
CREATE FOREIGN TABLE

prod=> \d
                     List of relations
 Schema |          Name           |     Type      | Owner
--------+-------------------------+---------------+--------
 public | ft_emp                  | foreign table | tom
 public | sys_stat_statements     | view          | system
 public | sys_stat_statements_all | view          | system
 public | t1                      | table         | system

四、访问外部表

1、Oracle端数据

11:06:32 SCOTT@prod>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

2、Kingbase端访问外部表

prod=# \d ft_emp
                           Foreign table "public.ft_emp"
 Column |            Type            | Collation | Nullable | Default | FDW options
--------+----------------------------+-----------+----------+---------+-------------
 empno  | integer                    |           |          |         |
 ename  | character varying(10 char) |           |          |         |
Server: to_ora
FDW options: (schema 'SCOTT', "table" 'EMP')

prod=> select * from ft_emp;
 empno | ename
-------+--------
  7369 | SMITH
  7499 | ALLEN
  7521 | WARD
  7566 | JONES
  7654 | MARTIN
  7698 | BLAKE
  7782 | CLARK
  7788 | SCOTT
  7839 | KING
  7844 | TURNER
  7876 | ADAMS
  7900 | JAMES
  7902 | FORD
  7934 | MILLER
(14 rows)

四、oracle_fdw访问外部视图

1、Oracle 创建视图

11:08:00 SCOTT@prod>create view emp_30 as select * from emp where deptno=30;

View created.

Elapsed: 00:00:00.05
11:08:02 SCOTT@prod>select * from emp_30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

2、kingbase创建外部表

prod=> create foreign table ft_emp30(empno integer,ename varchar(10))
prod-> server to_ora options(schema 'SCOTT',table 'EMP_30');
CREATE FOREIGN TABLE

如下图所示，创建到视图的外部表：

3、访问Oracle视图

prod=> \d
                     List of relations
 Schema |          Name           |     Type      | Owner
--------+-------------------------+---------------+--------
 public | ft_emp                  | foreign table | tom
 public | ft_emp30                | foreign table | tom
 public | sys_stat_statements     | view          | system
 public | sys_stat_statements_all | view          | system
 public | t1                      | table         | system
(5 rows)

prod=> select * from ft_emp30;
 empno | ename
-------+--------
  7499 | ALLEN
  7521 | WARD
  7654 | MARTIN
  7698 | BLAKE
  7844 | TURNER
  7900 | JAMES
(6 rows)
posted @   天涯客1224  阅读(7)  评论(0)  编辑  收藏  举报
相关博文：
· KingbaseES数据库运维案例---SCOTT用户及对象创建
· KingbaseES运维案例之--- 和 Oracle修改rename表名称对比
· KingbaseES file_dw 介绍
· KingabseES kingbase_fdw 跨库关联查询
· postgresql访问oracle数据
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
点击右上角即可分享
微信分享提示