金仓数据库使用dblink访问ORACLE

来源：这里教程网时间：2026-03-03 20:08:11 作者：

微信公众号：**老杜随笔**




如有问题或建议，请公众号留言;




**[如果你觉得对你有帮助，欢迎关注

一、说明

kingbase到oracle数据库的DBLINK，基于ODBC协议连接。以下为创建DBLINK过程案例。

二、配置kingbase的ODBC

2.1 环境说明

目标数据库： ORACLE21

源数据库： KingbaseES V008R006C008B002

源（目标）主机ip：LINUX 192.168.137.101

2.2 配置unixODBC

2.2.1、下载unixODBC

https://www.unixodbc.org/

在右边导航选择Download 选项，即可下载unixODBC-2.3.12.tar.gz。

2.2.2配置kingbase odbc

[kingbase@pgdb ~]$ odbcinst -j




unixODBC 2.3.1




DRIVERS............: /etc/odbcinst.ini




SYSTEM DATA SOURCES: /etc/odbc.ini




FILE DATA SOURCES..: /etc/ODBCDataSources




USER DATA SOURCES..: /home/kingbase/.odbc.ini




[root@pgdb ~]# chmod 660 /etc/odbcinst.ini

[kingbase@pgdb ~]$ odbcinst -j




unixODBC 2.3.1




DRIVERS............: /etc/odbcinst.ini




SYSTEM DATA SOURCES: /etc/odbc.ini




FILE DATA SOURCES..: /etc/ODBCDataSources




USER DATA SOURCES..: /home/kingbase/.odbc.ini




[root@pgdb ~]# chmod 660 /etc/odbcinst.ini




```

2.2.3上传Oracle的ODBC驱动到KingbaseES服务器







[oracle@pgdb ~]$ cd $ORACLE_HOME/lib




[oracle@pgdb lib]$ ls -lt libsqora.so.21.1




-rw-r--r--. 1 oracle oinstall 1082576 Jul 27 2021 libsqora.so.21.1




[oracle@pgdb lib]$ cp libsqora.so.21.1 /home/kingbase/app/Server/lib/libsqora.so.21.1

[kingbase@pgdb ~]$ cat /etc/odbcinst.ini




cat: /etc/odbcinst.ini: Permission denied




[kingbase@pgdb ~]$ exit




logout




[root@pgdb ~]# chmod 775 /etc/odbcinst.ini




[root@pgdb ~]# su - kingbase




Last login: Sat Jun 29 14:08:43 CST 2024 on pts/2




[kingbase@pgdb ~]$ cat /etc/odbcinst.ini




[Oracle ODBC Driver]




Description = ODBC for Oracle




Driver = /home/kingbase/app/Server/lib/libsqora.so.21.1

三、KingbaseES数据库创建dblink

3.1 修改KingbaseES数据库配置文件（data路径下kingbase.conf文件）

```




shared_preload_libraries = 'synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, kdb_ora_expr, sepapower, dblink, sys_kwr, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze, src_restrict,kdb_database_link'




ora_input_emptystr_isnull = on




ora_integer_div_returnfloat = on




[kingbase@pgdb data]$ pwd




/home/kingbase/app/data

将kdb_database_link 加入 shared_preload_libraries 中，注意 kdb_database_link 必须放在最后，因为，与其他项间有依赖关系。

3.2 重启数据库

```




[kingbase@pgdb data]$ sys_ctl restart -D data/




sys_ctl: directory "data" does not exist




[kingbase@pgdb data]$ cd ..




[kingbase@pgdb app]$ sys_ctl restart -D data/




sys_ctl: PID file "data/kingbase.pid" does not exist




Is server running?




trying to start server anyway




waiting for server to start....2024-06-29 14:19:59.504 CST [26388] LOG: sepapower extension initialized




2024-06-29 14:19:59.542 CST [26388] LOG: starting KingbaseES V008R006C008B0020 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit




2024-06-29 14:19:59.544 CST [26388] LOG: listening on IPv4 address "0.0.0.0", port 54321




2024-06-29 14:19:59.544 CST [26388] LOG: listening on IPv6 address "::", port 54321




2024-06-29 14:19:59.547 CST [26388] LOG: listening on Unix socket "/tmp/.s.KINGBASE.54321"




2024-06-29 14:19:59.676 CST [26388] LOG: redirecting log output to logging collector process




2024-06-29 14:19:59.676 CST [26388] HINT: Future log output will appear in directory "sys_log".

3.3登录数据库创建插件

```




[kingbase@pgdb ~]$ ksql -p 54321 -U system test 




Password for user system: 




Type "help" for help.










test=# 




test=# create extension kdb_database_link ;




CREATE EXTENSION




test=# 




test=# create extension oracle_fdw ;




CREATE EXTENSION




test=#

3.4创建DBLINK

```




test=# create public database link to_king_ora connect to 'system' identified by 'oracle'




<r' , Host = '192.168.137.101' , Port = 1521 , Dbname = 'orcl' , Dbtype = 'oracle');




CREATE DATABASE LINK




另一种方式可以配置




sys_database_link.conf文件




[kingbase@pgdb data]$ cat sys_database_link.conf




\#




\#Databaselink配置文件




\#databaselink是一组命名的连接参数。




\#可以在此文件中指定多个databaselinks。




\#每个从括号里的databaselink名开始。




\#随后的行有“param = value”模式的连接配置参数。




\#在此文件中包含了ORADB的示例配置。开始使用“#”的行是注释。




\#




[oradb]




dbtype=Oracle




dbname=orcl




DriverName="Oracle ODBC Driver"




host=192.168.137.101




port=1521

四、测试

test=# 




test=# 




test=# select * from tab@to_king_ora;





•      tname      | tabtype | clusterid 




-----------------------------+---------+-----------




 LOGMNR_SESSION_EVOLVE$   | TABLE  |     




 LOGMNR_GLOBAL$       | TABLE  |     




 LOGMNR_GT_TAB_INCLUDE$   | TABLE  |     




 LOGMNR_GT_USER_INCLUDE$   | TABLE  |     




 LOGMNR_GT_XID_INCLUDE$   | TABLE  |     




 LOGMNR_PDB_INFO$      | TABLE  |     




 LOGMNR_DID$         | TABLE  |     




 LOGMNR_UID$         | TABLE  |     




 LOGMNRGGC_GTLO       | TABLE  |     




 LOGMNRGGC_GTCS       | TABLE  |     




 LOGMNRC_DBNAME_UID_MAP   | TABLE  |     




 LOGMNR_LOG$         | TABLE  |