一、配置Oracle的ODBC
1、安装odbc,若有odbc则不用进行安装
yum -y install unixODBC
2、确认修改文件路径
odbcinst -j
3、上传Oracle的ODBC驱动到KingbaseES服务器
cp libsqora.so.19.1 /home/kingbase/KingbaseES/V8/Server/lib
chown -R kingbase:kingbase /home/kingbase/
4、配置驱动
vi /etc/odbcinst.ini
[Oracle ODBC Driver]
Description = ODBC for Oracle
Driver = /home/kingbase/KingbaseES/V8/Server/lib/libsqora.so.19.1
二、KingbaseES数据库创建dblink
1、修改KingbaseES数据库配置文件(data路径下kingbase.conf文件)
将kdb_database_link 加入 shared_preload_libraries 中,注意 kdb_database_link 必须放在最后,因为,与其他项间有依赖关系。
shared_preload_libraries='..., '
重启数据库
2、登录数据库创建插件
create extension kdb_database_link ;
create extension oracle_fdw ;
3、登录数据库创建dblink
1) create public database link to_orcl_1 connect to 'sjzl_bzqzz_cx' identified by 'Sjzl^bzQ2023' using ( DriverName = 'Oracle ODBC Driver' , Host = '10.66.4.204' , Port = 1521 , Dbname = 'sjzx' , Dbtype = 'oracle');
create public database link sjzl_bzqzz_19c connect to 'sjzl_bzqzz_cx' identified by 'Sjzl^bzQ2023' using ( DriverName = 'Oracle ODBC Driver' , Host = '10.66.4.204' , Port = 1521 , Dbname = 'sjzx' , Dbtype = 'oracle');
- 除上述方法,也可修改data路径下sys_database_link.conf文件
追加如下内容
[oradb]
dbtype=Oracle
dbname=sjzx
DriverName="Oracle ODBC Driver"
host=10.66.4.204
port=1521
create public database link to_ora204 connect to 'sjzl_bzqzz_cx' identified by 'Sjzl^bzQ2023' using 'oradb';
三、KingbaseES数据库使用dblink查询Oracle数据