Kingbase中Oracle_FDW 使用介绍

1. 下载 oracle_fdw 软件包

oracle_fdw_x86_64-V008R006C005B0023.tar.gz

2. 上传oracle_fdw软件包到指定目录并解压

cd /soft/kingbasedb
tar -zxvf oracle_fdw_x86_64-V008R006C005B0023.tar.gz

解压后会得到两个目录 share 和 lib

3. 修改 share 和 lib 目录的属组

cd /soft/kingbasedb
chown -R kingbase. share
chown -R kingbase. lib

4. 将解压出来的 share 和 lib 目录与数据库的 share 和 lib 目录合并

cd /soft/kingbasedb
cp -rv share /opt/Kingbase/ES/V8/Server
cp -rv lib /opt/Kingbase/ES/V8/Server

5. 修改kingbase用户的环境变量:解决创建扩展时的报错

在 .bash_profile 文件中增加环境变量LD_LIBRARY_PATH

su - kingbase
vim .bash_profile
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/Kingbase/ES/V8/Server/bin
export LD_LIBRARY_PATH=/opt/Kingbase/ES/V8/Server/lib:$LD_LIBRARY_PATH

export PATH
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

使配置文件生效

source .bash_profile

重新启动数据库，否则新环境变量不生效

sys_ctl restart -D /home/kingbase/data

如果不增加环境变量，创建扩展的时候会报错

test=# create extension oracle_fdw;
ERROR: could not load library "/opt/Kingbase/ES/V8/Server/lib/oracle_fdw.so": libnnz19.so: cannot open shared object file: No such file or directory

6. 创建扩展

create server to_orcl121 foreign data wrapper oracle_fdw options(dbserver '//136.0.10.121:1521/orcl');

##to_orcl121 是自定义名称
##//136.0.10.121:1521/orcl 是连接oracle数据库的字符串

7. 授权

grant usage on foreign server to_orcl121 to system;

##to_orcl121 是自定义名称
##system 是金仓数据库中的用户名

8. 创建mapping

create user mapping for system server to_orcl121 options(user 'scott',password 'tiger');

##指明金仓中的用户 system 使用server to_orcl121 与远程oracle数据库中哪一个用户相关联

9. 查看 server 和 mapping 的信息

select * from pg_foreign_server where srvname='to_orcl121';

oid | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions 
-------+------------+----------+--------+---------+------------+-------------------+-------------------------------------
16502 | to_orcl121 | 10 | 16501 | | | {system=U/system} | {dbserver=//136.0.10.121:1521/orcl}
(1 行记录) 

select * from pg_user_mappings where srvname='to_orcl121';
umid | srvid | srvname | umuser | usename | umoptions 
-------+-------+------------+--------+---------+-----------------------------
16503 | 16502 | to_orcl121 | 10 | system | {user=scott,password=tiger}
(1 行记录)

10. 创建外部表

create foreign table orcl121_emp 
(EMPNO integer,ENAME CHAR(10),
JOB CHAR(9),
MGR numeric(4),
HIREDATE DATE,
SAL numeric (7,2),
COMM numeric (7,2),
DEPTNO numeric (4)
) 
server to_orcl121 options(schema 'SCOTT', table 'EMP');

用户名 SCOTT 和表名 EMP 大小写敏感

11. 访问测试