合 在Oracle中通过dblink访问PG数据库
简介
在PG中访问PG可以通过dblink,在PG中访问Oracle可以通过oracle_fdw,访问MySQL可以通过mysql_fdw,具体过程可以参考:https://www.xmmup.com/pgzhongdefile_fdwpostgres_fdwhedblink.html
在Oracle访问SQL server需要配置Oracle Database Gateways透明网关,Oracle中访问SQL Server和MySQL的配置可以参考:https://www.xmmup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html
那么,在Oracle中访问PG该如何配置呢?请看下文。
Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的,需安装unixODBC和PostgreSQL ODBC驱动(它们的配置文件是:odbcinst.ini和odbc.ini),还需配置Oracle网络使用这个驱动。
环境准备
Oracle 11.2.0.4 CentOS 6.6 172.17.0.2
PG 13.8 ,Debian GNU/Linux 11 172.17.0.3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 创建Oracle主机,11.2.0.4环境 docker run -itd --name lhrora11204 -h lhrora11204 -p 3394:3389 \ -p 1524:1521 -p 1124:1158 -p 224:22 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init -- 创建PG主机,已安装PG 13数据库 docker rm -f lhrpg13 docker run --name lhrpg13 -h lhrpg13 -d -p 54326:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:13.8 docker exec -it lhrpg13 bash su - postgres create database lhrdb; \c lhrdb create table test(id int); insert into test values(1),(2); |
安装postgresql的odbc驱动包
安装ODBC驱动分成两部分:
1.安装unixODBC
1 2 | -- 可以直接安装 yum install -y unixODBC.x86_64 |
unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器。建议Linux环境安装unixODBC 2.3.7以上版本。可以使用isql --v
查询获取安装unixODBC版本。在CentOS 7中,通过yum安装后版本为2.3.7-;在CentOS 6中,通过yum安装后版本为2.2.14,也可以使用,若使用编译安装,则具体安装方法如下,在root用户下进行操作:
Ø 解压文件:tar -xf unixODBC-2.3.7.tar.gz
Ø 配置安装:cd unixODBC-2.3.7;./configure --prefix=/usr/local/odbc
Ø 编译安装:make && make install
Ø 默认安装到/usr/local/odbc下
Ø 退出当前会话,重新登录查询ODBC版本isql --v
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
SocketBufferSize = 4096
FetchBufferSize = 500
这个参数是什么含义?
增加性能的,可以不用配置,参考:https://docs.oracle.com/en/database/oracle/oracle-database/19/unxar/using-oracle-ODBC-driver.html#GUID-CF96CB6C-43BF-4BD2-919C-EE005B64FEE3
- Fetch Buffer Size
Set the Fetch Buffer Size (FetchBufferSize) in the odbc.ini file to a value specified in bytes. This value is the amount of memory needed that determines how many rows of data Oracle ODBC Driver pre-fetches at a time from an Oracle Database to the client's cache regardless of the number of rows the application program requests in a single query, thus improving performance.
There is an improvement in the response time of applications that typically fetch fewer than 20 rows of data at a time, particularly over slow network connections or from heavily loaded servers. Setting this too high can have an adverse effect on response time or consume large amounts of memory. The default is 64,000 bytes. You should choose an optimal value for the application.
When the LONG and LOB data types are present, the number of rows pre-fetched by Oracle ODBC Driver is not determined by the Fetch Buffer Size. The inclusion of the LONG and LOB data types minimizes the performance improvement and could result in excessive memory use. Oracle ODBC Driver ignores the Fetch Buffer Size and only pre-fetches a set number of rows in the presence of the LONG and LOB data types.
厉害啊,麦苗老师。那个目录是真没想到,按以前版本的目录确实没生效。21c感觉很多地方不一样。不过有个问题,Windows下的管理工具toad,dbeaver显示中文都是乱码,而且看了一下二进制没啥关联,不知道咋形成的。sqlplus看到的中文不是乱码,但插入数据的时候,一个汉字不行,得两个。
SYS@LHRCDB> insert into "public"."t1"@hgdb values(10,'我');
ERROR:
ORA-01756: quoted string not properly terminated
SYS@LHRCDB> insert into "public"."t1"@hgdb values(10,'我我');
1 row created.