打渔网站建设,平顶山建站公司,网站设计的尺寸,深圳制作软件概述 Oracle_fdw 是一种postgresql外部表插件#xff0c;可以读取到Oracle上面的数据。是一种非常方便且常见的pg与Oracle的同步数据的方法
Oracle_fdw 适用场景#xff1a;
Oracle_fdw 是一个开源的 Foreign Data Wrapper (FDW)#xff0c;主要用于在 PostgreSQL 数据库中…概述 Oracle_fdw 是一种postgresql外部表插件可以读取到Oracle上面的数据。是一种非常方便且常见的pg与Oracle的同步数据的方法
Oracle_fdw 适用场景
Oracle_fdw 是一个开源的 Foreign Data Wrapper (FDW)主要用于在 PostgreSQL 数据库中访问和操作 Oracle 数据库中的数据。适用场景包括但不限于 跨数据库查询整合 当你有一个混合数据库环境既有 PostgreSQL 又有 Oracle 数据库需要在 PostgreSQL 中直接查询或联合查询 Oracle 数据库中的表无需手动数据迁移或ETL流程。 数据迁移与同步 在迁移项目中Oracle_fdw 可以作为一个临时解决方案让你在迁移过程中逐步将数据迁移到 PostgreSQL同时保持业务连续性期间可以继续在 PostgreSQL 中查询和使用 Oracle 中的数据。 数据仓库与 BI 场景 如果你的数据仓库基于 PostgreSQL 构建但一部分数据源还在 Oracle 数据库中可以使用 Oracle_fdw 把 Oracle 数据作为外部表集成到数据仓库中便于统一进行数据分析和报表生成。 应用集成 在企业应用集成场景下如果有遗留系统依赖于 Oracle 数据库而新的应用使用 PostgreSQLOracle_fdw 可以帮助新旧系统之间实现平滑过渡减少直接接口对接的工作量。 异构数据库备份与冗余 Oracle_fdw 可以用于在 PostgreSQL 中建立 Oracle 数据库的实时或定期备份提高数据冗余性和可用性。
总之Oracle_fdw 在需要跨越 PostgreSQL 和 Oracle 数据库边界进行数据交互和操作的场景中发挥着重要作用。通过它可以简化数据集成、查询和管理工作同时保持数据源的独立性和灵活性。
简单来说Oracle_fdw 就像是一个“翻译官”专门用来帮助 PostgreSQL 数据库跟 Oracle 数据库交朋友、说“同一种语言”。在实际使用中比如 当你的公司里一部分数据存放在了 Oracle 数据库里另一部分在 PostgreSQL 中如果想在一个地方比如 PostgreSQL直接查询两个数据库的数据就像查自家的东西一样方便这时候就需要 Oracle_fdw 这个工具帮忙它能让 PostgreSQL 理解并获取到 Oracle 数据库中的信息。 或者在升级系统时新的软件用的是 PostgreSQL但是老系统数据在 Oracle 里面不需要先把所有数据都搬到新数据库而是可以直接通过 Oracle_fdw 让新系统边工作边读取老系统的数据。 再比如做数据分析时你可以把 Oracle 里的数据当作是 PostgreSQL 数据库的一部分来处理这样就不必每次分析前都手动搬数据大大提高了效率。
所以Oracle_fdw 主要就是解决不同数据库之间数据共享、查询和整合的问题让数据库之间的交流不再困难。 案例简介 Oracle_fdw 的编译依赖pg_config和Oracle的客户端环境
pg_config 是什么呢其实就是postgresql的一个可执行程序该程序提供postgresql服务的基本信息包括各类运行库
[rootcentos7 oracle_fdw-ORACLE_FDW_2_2_0]# pg_config
BINDIR /usr/pgsql-12/bin
DOCDIR /usr/pgsql-12/doc
HTMLDIR /usr/pgsql-12/doc/html
INCLUDEDIR /usr/pgsql-12/include
PKGINCLUDEDIR /usr/pgsql-12/include
INCLUDEDIR-SERVER /usr/pgsql-12/include/server
LIBDIR /usr/pgsql-12/lib
PKGLIBDIR /usr/pgsql-12/lib
LOCALEDIR /usr/pgsql-12/share/locale
MANDIR /usr/pgsql-12/share/man
SHAREDIR /usr/pgsql-12/share
SYSCONFDIR /etc/sysconfig/pgsql
PGXS /usr/pgsql-12/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE --enable-rpath --prefix/usr/pgsql-12 --includedir/usr/pgsql-12/include --libdir/usr/pgsql-12/lib --mandir/usr/pgsql-12/share/man --datadir/usr/pgsql-12/share --with-icu --with-llvm --with-perl --with-python --with-tcl --with-tclconfig/usr/lib64 --with-openssl --with-pam --with-gssapi --with-includes/usr/include --with-libraries/usr/lib64 --enable-nls --enable-dtrace --with-uuide2fs --with-libxml --with-libxslt --with-ldap --with-selinux --with-systemd --with-system-tzdata/usr/share/zoneinfo --sysconfdir/etc/sysconfig/pgsql --docdir/usr/pgsql-12/doc --htmldir/usr/pgsql-12/doc/html CFLAGS-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE2 -fexceptions -fstack-protector-strong --paramssp-buffer-size4 -grecord-gcc-switches -m64 -mtunegeneric LDFLAGS-Wl,--as-needed LLVM_CONFIG/usr/lib64/llvm5.0/bin/llvm-config CLANG/opt/rh/llvm-toolset-7/root/usr/bin/clang PKG_CONFIG_PATH:/usr/lib64/pkgconfig:/usr/share/pkgconfig PYTHON/usr/bin/python2
CC gcc -stdgnu99
CPPFLAGS -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE2 -fexceptions -fstack-protector-strong --paramssp-buffer-size4 -grecord-gcc-switches -m64 -mtunegeneric
CFLAGS_SL -fPIC
LDFLAGS -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,/usr/pgsql-12/lib,--enable-new-dtags
LDFLAGS_EX
LDFLAGS_SL
LIBS -lpgcommon -lpgport -lpthread -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION PostgreSQL 12.4
[rootcentos7 oracle_fdw-ORACLE_FDW_2_2_0]# whereis pg_config
pg_config: /usr/pgsql-12/bin/pg_configOracle的客户端环境指的是Oracle的客户端接口sdkOracle客户端运行时需要的库 这些东西
在本例中安装的postgresql版本是12.4版本Oracle安装的版本是12c而oracle-fdw安装的版本是2.2经实验2.2版本以上和postgresql12版本有冲突主要是某些新功能方面的冲突
而操作系统仍然是centos7下面将就Oracle服务器的安装部署和oracle-fdw的安装部署以及初步使用做一个介绍
Oracle服务器部署在192.168.123.13这个服务器上以docker形式部署
postgresql服务器部署在192.168.123.17这个服务器上以yum形式部署
Oracle客户端环境当然是在哪个postgresql服务器上使用就在哪部署了是在192.168.123.17这个服务器上以编译方式部署
链接https://pan.baidu.com/s/11W_QqZNOIogigHIxp7byNQ?pwdpost 提取码post 本次实践的所有文件都放在了百度网盘有需要复现的可自由下载使用
一
Oracle服务端的部署在13服务器上部署
使用docker急速搭建Oracle测试环境完全离线_docker-entrypoint-initdb.d-CSDN博客
这篇博客写的比较久远有些地方和概念讲的并不是特别清楚因此本文再次重复一遍
1
docker环境部署
将docker-19.03.9.tgz这个压缩包上传到服务器13上并解压将解压的所有文件放置到/usr/local/bin 这个系统目录下
mv docker/* /usr/local/bin/
将docker-compose这个docker镜像编排部署工具也放置到/usr/local/bin 目录下并赋予可执行权限
mv docker-compose /usr/loca/bin/
chmod ax /usr/local/bin/docker-compose
将docker加入系统服务设置开启启动并启动docker服务。 文件内容如下
cat /etc/systemd/system/docker.serviceEOF
[Unit]
DescriptionDocker Application Container Engine
Documentationhttps://docs.docker.com
Afternetwork-online.target firewalld.service
Wantsnetwork-online.target[Service]
Typenotify
ExecStart/usr/local/bin/dockerd --graph/var/lib/docker
ExecReload/bin/kill -s HUP $MAINPID
LimitNOFILEinfinity
LimitNPROCinfinity
LimitCOREinfinity
TimeoutStartSec0
Delegateyes
KillModeprocess
Restarton-failure
StartLimitBurst3
StartLimitInterval60s[Install]
WantedBymulti-user.target
EOF 创建docker的配置文件该文件作用是国内的镜像站加速以及docker日志控制作用
mkdir /etc/docker cat /etc/docker/daemon.jsonEOF
{registry-mirrors: [https://b0j89uo8.mirror.aliyuncs.com],exec-opts:[native.cgroupdriversystemd],log-driver:json-file,log-opts: {max-size: 100m
},storage-driver: overlay2
}
EOF 将docker服务加入开机自启并启动docker服务最后查看docker服务是否正常
systemctl enable docker
systemctl start docker
systemctl status docker
输出如下
[rootcentos3 ~]# systemctl status docker
● docker.service - Docker Application Container EngineLoaded: loaded (/etc/systemd/system/docker.service; enabled; vendor preset: disabled)Active: active (running) since Fri 2024-04-05 07:34:39 CST; 1min 14s agoDocs: https://docs.docker.comMain PID: 11621 (dockerd)Tasks: 54Memory: 54.2MCGroup: /system.slice/docker.service├─11621 /usr/local/bin/dockerd --graph/var/lib/docker├─11631 containerd --config /var/run/docker/containerd/containerd.toml --log-level info├─11779 /usr/local/bin/docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 53432 -container-ip 172.18.0.2 -container-port 1521├─11793 /usr/local/bin/docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 36888 -container-ip 172.18.0.2 -container-port 8080└─11799 containerd-shim -namespace moby -workdir /var/lib/docker/containerd/daemon/io.containerd.runtime.v1.linux/moby/0453420a43f67ede8380d091ec3b2a1c0c642034c2a216d358ce4c62c96a12c0 -address /var/run/docker/containerd/containerd.sock -containerd-binary /usr/local/bin/containerd -runtime-root /var/run/docker/runtime-...Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.03890065708:00 levelinfo msgccResolverWrapper: sending update to cc: {[{unix:///var/run/docker/containerd/containerd.sock 0 nil}] nil} modulegrpc
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.03890630108:00 levelinfo msgClientConn switching balancer to \pick_first\ modulegrpc
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.06024791408:00 levelinfo msgLoading containers: start.
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.14425279108:00 levelinfo msgDefault bridge (docker0) is assigned with an IP address 172.17.0.0/16. Daemon option --bip can be used to set a preferred IP address
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.20342934208:00 levelinfo msgshim containerd-shim started address/containerd-shim/moby/0453420a43f67ede8380d091ec3b2a1c0c642034c2a216d358ce4c62c96a12c0/shim.sock debugfalse pid11799
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.44559259908:00 levelinfo msgLoading containers: done.
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.45898683508:00 levelinfo msgDocker daemon commit9d988398e7 graphdriver(s)overlay2 version19.03.9
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.45904666108:00 levelinfo msgDaemon has completed initialization
Apr 05 07:34:39 centos3 dockerd[11621]: time2024-04-05T07:34:39.47276606408:00 levelinfo msgAPI listen on /var/run/docker.sock
Apr 05 07:34:39 centos3 systemd[1]: Started Docker Application Container Engine.2
Oracle12c服务端的部署
docker-12c-oracle.tar 这个镜像包上传到13服务器上后直接导入导入命令为
docker load docker-12c-oracle.tar
生成docker-compose使用的编排文件命令如下
cat test.yamlEOF
version: 3
services:oracle:restart: alwaysimage: hub.c.163.com/springwen/oracle12ccontainer_name: oraclevolumes:- /usr/local/oracle/data:/u01/app/oracle- /usr/local/oracle/source:/docker-entrypoint-initdb.denvironment:- TZAsia/Shanghai- DBCA_TOTAL_MEMORY16192- IMPORT_FROM_VOLUMEtrueports:- 53432:1521- 36888:8080logging:driver: json-fileoptions:max-size: 1g
EOF 通过docker-compose 启动镜像并测试连接是否正常如果命令不带d参数将会是前台启动占用一个shell窗口
docker-compose -f test.yaml up -d 日志以如下结尾表示初始化并启动成功
oracle | Import finished
oracle |
oracle | Database ready to use. Enjoy! ;)那么假如这个数据库初始化的并不满意想要重新初始化 如何操作呢
这个就比较简单了先停止容器然后将挂载目录删除后在强制删除容器就可以了命令如下
docker-compose -f test.yaml down
rm -rf /usr/local/oracle/*
docker rm -f $(docker ps -aq) 删除后在执行上面的up命令就可以重新初始化了剩下的就是在Windows使用 0 sqldeveloper.zip这个客户端连接了该客户端使用非常简单
只需要解压就可以了连接的配置界面是这样的 连接名随意自己高兴就可以了用户名是sys这个是超级特权用户口令是oracle点击保存口令连接类型是基本的角色是sysdba服务器IP是13端口是上面的yaml文件定义的
53432选择sidsid是xe
在Oracle数据库中SIDSystem Identifier和服务名Service Name都用于标识数据库实例但它们的用途和表现形式有所不同 SID (System Identifier) SID是Oracle早期版本中用于标识数据库实例的唯一标识符它是Oracle数据库实例启动时分配的一个内部数字标识。在数据库的配置文件如init.ora或spfile中指定。在连接字符串中使用传统的连接方式时可以用SID来标识要连接的数据库实例例如jdbc:oracle:thin:hostname:port:SID。 服务名 (Service Name) 自Oracle Database 9i及以后版本服务名逐渐取代了SID作为连接数据库的标准方式。服务名不仅标识了数据库实例还反映了数据库的逻辑服务层次它可以与多个数据库实例相关联如在Oracle RAC环境中。服务名在Oracle Net服务配置文件如tnsnames.ora或通过LDAP中定义可以包含数据库实例的别名便于管理和识别。在连接字符串中使用服务名连接数据库例如jdbc:oracle:thin://hostname:port/service_name。
总结来说SID更多是数据库实例内在的标识而服务名是面向客户端连接时更加灵活且易于管理的逻辑服务标识。在现代Oracle数据库管理中推荐使用服务名而非SID进行连接。
查询SID需要超级权限用户查询例如sys用户
SELECT DISTINCT service_name FROM v$services;查询service名称如果有多个的话同样需要超级权限用户
SELECT value FROM v$parameter WHERE name service_names;那么如果是使用服务名配置navicat的连接的话就需要改成大写了大小写是敏感的 这里还有一个概念是比较特殊的那就是schema 模式可以简单的认为一个用户就是一个模式模式之间相互隔离除非连接的用户是超级用户
在Oracle数据库中Schema是一个逻辑概念它代表了一组相关的数据库对象的集合。这些对象可能包括但不限于
表Tables视图Views序列Sequences存储过程Stored Procedures函数Functions包Packages同义词Synonyms索引Indexes表簇Clusters数据库链接Database Links
每个Oracle数据库用户都有一个与其同名的默认Schema。当创建一个Oracle用户时实际上也创建了一个Schema。用户和Schema是绑定在一起的一个用户拥有的所有数据库对象都在其自身的Schema内。不同用户创建的对象分别存放在各自对应的Schema中而且各个Schema是相互隔离的一个用户如果没有授权就不能访问另一个用户的Schema中的对象。
在Oracle中通过以下方式可以明确地引用Schema中的对象 schema_name.object_name
例如如果有一个名为HR的Schema其中有一个名为EMPLOYEES的表那么完整地引用这个表的语法将是 HR.EMPLOYEES
这样设计的好处在于可以实现数据的逻辑隔离方便不同用户或应用程序之间的数据管理和安全性控制。每个Schema都可以有自己的权限体系确保数据的安全和完整性。 ./sqlplus sys/oracle(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.123.13)(PORT53432))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMExe))) as sysdba 二、
Oracle客户端的部署在数据库所在服务器17上部署
在安装Oracle的客户端之前需要确保postgresql服务是正常的postgresql的安装见博客Linux|centos7-postgresql数据库|yum安装数据库和配置repmgr高可用集群以及repmgr的日常管理工作-CSDN博客
1
上传instantclient-basic-linux.x64-19.20.0.0.0dbru.zipinstantclient-sdk-linux.x64-19.20.0.0.0dbru.zipinstantclient-sqlplus-linux.x64-19.20.0.0.0dbru.ziporacle_fdw-ORACLE_FDW_2_2_0.zip 这四个文件到17服务器上全部解压
创建目录/opt/oracle 将上述解压的目录移动到此目录下并改名为instantclient
mkdir /opt/oracle
mv instantclient_19_20 /opt/oracle/
编辑环境变量文件/etc/profile 末尾添加如下内容
export ORACLE_HOME/opt/oracle/instantclient
export OCI_LIB_DIR$ORACLE_HOME
export OCI_INC_DIR$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH$ORACLE_HOME:$LD_LIBRARY_PATH激活环境变量
source /etc/profile
进入oracle_fdw-ORACLE_FDW_2_2_0.zip解压后的目录执行编译这里我就把命令和输出都放一起了
[rootcentos10 ~]# cd oracle_fdw-ORACLE_FDW_2_2_0
[rootcentos10 oracle_fdw-ORACLE_FDW_2_2_0]# make
gcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE2 -fexceptions -fstack-protector-strong --paramssp-buffer-size4 -grecord-gcc-switches -m64 -mtunegeneric -fPIC -I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include -I/opt/oracle/instantclient/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_utils.o oracle_utils.c
gcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE2 -fexceptions -fstack-protector-strong --paramssp-buffer-size4 -grecord-gcc-switches -m64 -mtunegeneric -fPIC -I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include -I/opt/oracle/instantclient/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_gis.o oracle_gis.c
gcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE2 -fexceptions -fstack-protector-strong --paramssp-buffer-size4 -grecord-gcc-switches -m64 -mtunegeneric -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-12/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,/usr/pgsql-12/lib,--enable-new-dtags -L/opt/oracle/instantclient -L/opt/oracle/instantclient/bin -L/opt/oracle/instantclient/lib -L/opt/oracle/instantclient/lib/amd64 -lclntsh -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include -I/opt/oracle/instantclient/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fltothin -emit-llvm -c -o oracle_fdw.bc oracle_fdw.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include -I/opt/oracle/instantclient/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fltothin -emit-llvm -c -o oracle_utils.bc oracle_utils.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include -I/opt/oracle/instantclient/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fltothin -emit-llvm -c -o oracle_gis.bc oracle_gis.c
[rootcentos10 oracle_fdw-ORACLE_FDW_2_2_0]# make install
/usr/bin/mkdir -p /usr/pgsql-12/lib
/usr/bin/mkdir -p /usr/pgsql-12/share/extension
/usr/bin/mkdir -p /usr/pgsql-12/share/extension
/usr/bin/mkdir -p /usr/pgsql-12/doc/extension
/usr/bin/install -c -m 755 oracle_fdw.so /usr/pgsql-12/lib/oracle_fdw.so
/usr/bin/install -c -m 644 .//oracle_fdw.control /usr/pgsql-12/share/extension/
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql /usr/pgsql-12/share/extension/
/usr/bin/install -c -m 644 .//README.oracle_fdw /usr/pgsql-12/doc/extension/
/usr/bin/mkdir -p /usr/pgsql-12/lib/bitcode/oracle_fdw
/usr/bin/mkdir -p /usr/pgsql-12/lib/bitcode/oracle_fdw/
/usr/bin/install -c -m 644 oracle_fdw.bc /usr/pgsql-12/lib/bitcode/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_utils.bc /usr/pgsql-12/lib/bitcode/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_gis.bc /usr/pgsql-12/lib/bitcode/oracle_fdw/./
cd /usr/pgsql-12/lib/bitcode /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-actionthinlink -o oracle_fdw.index.bc oracle_fdw/oracle_fdw.bc oracle_fdw/oracle_utils.bc oracle_fdw/oracle_gis.bc可以看到上面配置的环境变量也在编译过程里例如-I/opt/oracle/instantclient/sdk/include -I/opt/oracle/instantclient/oci/include
现在使用Oracle的客户端试着主动连接一下Oracle的服务端连接成功代表Oracle客户端没有什么问题可以正常使用
[rootcentos10 instantclien]# pwd
/opt/instantclien
[rootcentos10 instantclien]# ./sqlplus sys/oracle(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.123.13)(PORT53432))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMExe))) as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 6 05:05:18 2024
Version 19.20.0.0.0Copyright (c) 1982, 2022, Oracle. All rights reserved.Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit ProductionSQL 三
创建Oracle数据库的测试表
1创建普通用户zsk并赋予建表建索引连接数据库等权限
CREATE USER zsk IDENTIFIED BY 123456
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
ALTER USER zsk QUOTA UNLIMITED ON USERS;-- 授予用户基本的连接权限
GRANT CREATE SESSION TO zsk;-- 如果用户需要创建表、序列等对象还需进一步授予资源权限
GRANT CREATE TABLE TO zsk;
GRANT CREATE SEQUENCE TO zsk;-- 如果用户需要执行DML操作如INSERT, UPDATE, DELETE等
GRANT INSERT ANY TABLE TO zsk;
GRANT UPDATE ANY TABLE TO zsk;
GRANT DELETE ANY TABLE TO zsk;
-- 若用户需要执行DDL操作如创建索引、触发器等
GRANT CREATE ANY INDEX TO zsk;
GRANT CREATE TRIGGER TO zsk;
2
切换为普通用户zsk后创建相关表和索引总共创建了5个表分别是departmentjobs,employees,test,t100
CREATE TABLE department( dept_id INTEGER NOT NULL PRIMARY KEY, dept_name VARCHAR(50) NOT NULL) ;
CREATE TABLE jobs( job_id INTEGER NOT NULL PRIMARY KEY, job_title VARCHAR(50) NOT NULL) ;
CREATE TABLE employees( emp_id INTEGER NOT NULL PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, sex VARCHAR(10) NOT NULL, dept_id INTEGER NOT NULL, manager INTEGER, hire_date DATE NOT NULL, job_id INTEGER NOT NULL, salary NUMERIC(8,2) NOT NULL, bonus NUMERIC(8,2), email VARCHAR(100) NOT NULL, CONSTRAINT ck_emp_sex CHECK (sex IN (男, 女)), CONSTRAINT ck_emp_salary CHECK (salary 0), CONSTRAINT uk_emp_email UNIQUE (email), CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id), CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES jobs(job_id), CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employees(emp_id)) ;
CREATE INDEX idx_emp_name ON employees(emp_name);
CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_emp_jobs ON employees(job_id);
CREATE INDEX idx_emp_manager ON employees(manager);
INSERT INTO department(dept_id, dept_name) VALUES (1, 行政管理部);
INSERT INTO department(dept_id, dept_name) VALUES (2, 人力资源部);
INSERT INTO department(dept_id, dept_name) VALUES (3, 财务部);
INSERT INTO department(dept_id, dept_name) VALUES (4, 研发部);
INSERT INTO department(dept_id, dept_name) VALUES (5, 销售部);
INSERT INTO department(dept_id, dept_name) VALUES (6, 保卫部);INSERT INTO jobs(job_id, job_title) VALUES (1, 总经理);
INSERT INTO jobs(job_id, job_title) VALUES (2, 副总经理);
INSERT INTO jobs(job_id, job_title) VALUES (3, 人力资源总监);
INSERT INTO jobs(job_id, job_title) VALUES (4, 人力资源专员);
INSERT INTO jobs(job_id, job_title) VALUES (5, 财务经理);
INSERT INTO jobs(job_id, job_title) VALUES (6, 会计);
INSERT INTO jobs(job_id, job_title) VALUES (7, 开发经理);
INSERT INTO jobs(job_id, job_title) VALUES (8, 程序员);
INSERT INTO jobs(job_id, job_title) VALUES (9, 销售经理);
INSERT INTO jobs(job_id, job_title) VALUES (10, 销售人员);INSERT INTO employees VALUES (1, 刘备, 男, 1, NULL, TO_DATE(2000-01-01, YYYY-MM-DD), 1, 30000, 10000, liubeishuguo.com);
INSERT INTO employees VALUES (2, 关羽, 男, 1, 1, TO_DATE(2000-01-01, YYYY-MM-DD), 2, 26000, 10000, guanyushuguo.com);
INSERT INTO employees VALUES (3, 张飞, 男, 1, 1, TO_DATE(2000-01-01, YYYY-MM-DD), 2, 24000, 10000, zhangfeishuguo.com);
INSERT INTO employees VALUES (4, 诸葛亮, 男, 2, 1, TO_DATE(2006-03-15, YYYY-MM-DD), 3, 24000, 8000, zhugeliangshuguo.com);
INSERT INTO employees VALUES (5, 黄忠, 男, 2, 4, TO_DATE(2008-10-25, YYYY-MM-DD), 4, 8000, NULL, huangzhongshuguo.com);
INSERT INTO employees VALUES (6, 魏延, 男, 2, 4, TO_DATE(2007-04-01, YYYY-MM-DD), 4, 7500, NULL, weiyanshuguo.com);
INSERT INTO employees VALUES (7, 孙尚香, 女, 3, 1, TO_DATE(2002-08-08, YYYY-MM-DD), 5, 12000, 5000, sunshangxiangshuguo.com);
INSERT INTO employees VALUES (8, 孙丫鬟, 女, 3, 7, TO_DATE(2002-08-08, YYYY-MM-DD), 6, 6000, NULL, sunyahuanshuguo.com);
INSERT INTO employees VALUES (9, 赵云, 男, 4, 1, TO_DATE(2005-12-19, YYYY-MM-DD), 7, 15000, 6000, zhaoyunshuguo.com);
INSERT INTO employees VALUES (10, 廖化, 男, 4, 9, TO_DATE(2009-02-17, YYYY-MM-DD), 8, 6500, NULL, liaohuashuguo.com);
INSERT INTO employees VALUES (11, 关平, 男, 4, 9, TO_DATE(2011-07-24, YYYY-MM-DD), 8, 6800, NULL, guanpingshuguo.com);
INSERT INTO employees VALUES (12, 赵氏, 女, 4, 9, TO_DATE(2011-11-10, YYYY-MM-DD), 8, 6600, NULL, zhaoshishuguo.com);
INSERT INTO employees VALUES (13, 关兴, 男, 4, 9, TO_DATE(2011-07-30, YYYY-MM-DD), 8, 7000, NULL, guanxingshuguo.com);
INSERT INTO employees VALUES (14, 张苞, 男, 4, 9, TO_DATE(2012-05-31, YYYY-MM-DD), 8, 6500, NULL, zhangbaoshuguo.com);
INSERT INTO employees VALUES (15, 赵统, 男, 4, 9, TO_DATE(2012-05-03, YYYY-MM-DD), 8, 6000, NULL, zhaotongshuguo.com);
INSERT INTO employees VALUES (16, 周仓, 男, 4, 9, TO_DATE(2010-02-20, YYYY-MM-DD), 8, 8000, NULL, zhoucangshuguo.com);
INSERT INTO employees VALUES (17, 马岱, 男, 4, 9, TO_DATE(2014-09-16, YYYY-MM-DD), 8, 5800, NULL, madaishuguo.com);
INSERT INTO employees VALUES (18, 法正, 男, 5, 2, TO_DATE(2017-04-09, YYYY-MM-DD), 9, 10000, 5000, fazhengshuguo.com);
INSERT INTO employees VALUES (19, 庞统, 男, 5, 18, TO_DATE(2017-06-06, YYYY-MM-DD), 10, 4100, 2000, pangtongshuguo.com);
INSERT INTO employees VALUES (20, 蒋琬, 男, 5, 18, TO_DATE(2018-01-28, YYYY-MM-DD), 10, 4000, 1500, jiangwanshuguo.com);
INSERT INTO employees VALUES (21, 黄权, 男, 5, 18, TO_DATE(2018-03-14, YYYY-MM-DD), 10, 4200, NULL, huangquanshuguo.com);
INSERT INTO employees VALUES (22, 糜竺, 男, 5, 18, TO_DATE(2018-03-27, YYYY-MM-DD), 10, 4300, NULL, mizhushuguo.com);
INSERT INTO employees VALUES (23, 邓芝, 男, 5, 18, TO_DATE(2018-11-11, YYYY-MM-DD), 10, 4000, NULL, dengzhishuguo.com);
INSERT INTO employees VALUES (24, 简雍, 男, 5, 18, TO_DATE(2019-05-11, YYYY-MM-DD), 10, 4800, NULL, jianyongshuguo.com);
INSERT INTO employees VALUES (25, 孙乾, 男, 5, 18, TO_DATE(2018-10-09, YYYY-MM-DD), 10, 4700, NULL, sunqianshuguo.com);
create table test as
select rownum as id,
to_char(sysdate rownum/24/3600, yyyy-mm-dd hh24:mi:ss) as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string(x, 20) random_string
from dual
connect by level 500000;create table t100 (id number not null,address varchar(500) not null,sales number not null);
insert into t100 select trunc(dbms_random.value(1,1000000)) as id,(dbms_random.string(a,500)) as address,trunc(dbms_random.value(1,3000000)) as sales from dual connect by level 10000
四、
在17服务器上创建外部表
总共需要分三步第一步是创建server第二步是创建用户映射第三步是创建外部表
1、创建server
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver //192.168.123.13:53432/xe,read_only true); -- oradb 自定义一个 SERVER NAME -- OPTIONS: 有3个参数--dbserver、isolation_level、nchar -- dbserver(必需)定义连接 Oracle 数据库的连接字符串。 -- isolation_level(可选默认为 serializable): 在 Oracle 数据库中使用的事务隔离级别可设置的参数值 serializable、read_committed、read_only。 -- nchar(可选默认为 off): 是否开启 Oracle 端的字符转换这个参数的开启对性能有很大影响。 -- 这个 SERVER 可以赋权给普通用户使用 例如普通用户pguser GRANT USAGE ON FOREIGN SERVER oradb TO pguser; 2、创建用户映射
postgres# create user MAPPING FOR postgres server oradb OPTIONS (user zsk, password 123456);
CREATE USER MAPPING -- postgres PostgreSQL 中已存在的用户 -- oradb 已创建的 SERVER NAME -- OPTIONS 有2个参数--user、password -- user(必需)Oracle 用户名 -- password(必需)Oracle 用户的密码 可以查看到连接密码 postgres# \deu List of user mappings Server | User name | FDW options ----------------------------------------------------------- oradb | postgres | (user zsk, password 123456) zsk | postgres | (user zsk, password 123456) (2 rows) 这个名为zsk的server需要删除命令为 postgres# drop server zsk cascade ; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to user mapping for postgres on server zsk drop cascades to foreign table department drop cascades to foreign table employees drop cascades to foreign table jobs DROP SERVER 3、创建外部表
两种方式第一种是create命令创建第二种是import命令创建
1
create命令创建
DROP FOREIGN TABLE ora_emp_list
CREATE FOREIGN TABLE ora_emp_list( empno NUMERIC(4,0) OPTIONS (key true) NOT NULL, ename VARCHAR(10), sex VARCHAR(10) NOT NULL, dept_id VARCHAR(10) NOT NULL, manager VARCHAR(10), hire_date TIMESTAMP, jobid VARCHAR(9), sal VARCHAR(9), bonus VARCHAR(9), email VARCHAR(100) NOT NULL)
SERVER oradb OPTIONS (schema ZSK, table EMPLOYEES); 输出如下 需要注意 如果数据的长度超过了实际的列长度就会出现运行时错误。另外请注意数据类型的行为可能不同例如浮点数据类型和日期时间数据类型中的分数舍入。 请记住默认情况下Oracle 中 CHAR 和 VARCHAR2 类型的长度以字节为单位指定而 PostgreSQL 的 CHAR、VARCHAR 和 TEXT 类型的长度以字符为单位指定。 外部表的字段名不需要与 Oracle 的表保持一致但是字段顺序需要与 Oracle 的表保持一致外部表的主键需要与 Oracle 的表保持一致OPTIONS 属性里的 schema 和 table 名称必须大写不然 PostgreSQL 端操作外部表会报 ‘ORA-00942: table or view does not exist’。OPTIONS 属性里的 schema 和 table 必须用单引号必须定义 oracle_fdw 可以转换的列对应关系如下 查询一下看看外部表是否正确获取到了可以看到确实获取到了但列名和Oracle的是不一样的还算 PostgreSQL 外部表是否可以只关联 Oracle 表的某几个字段呢比如示例中的 emp_list 表有10个字段我只想关联3个字段emp_id,emp_name,dept_id
DROP FOREIGN TABLE ora_emp_listCREATE FOREIGN TABLE ora_emp_list( empno NUMERIC(4,0) OPTIONS (key true) NOT NULL, ename VARCHAR(10), deptno NUMERIC(2,0))
SERVER oradb OPTIONS (table (SELECT emp_id,emp_name,dept_id FROM EMPLOYEES));
查询一下可以正确获取到外部表 但此时有一个问题外部表的更改会作用到Oracle的源表如何将外部表更改为只读表呢
如果您想限制在PostgreSQL中对ora_emp_list外部表的写入操作可以考虑以下方案 权限控制 限制对包含该外部表的数据库角色的写权限使其只能执行查询操作在本例中当然是在Oracle服务器端对zsk这个映射用户的权限做限制就可以只读了 应用程序层面限制 在应用程序代码中确保只执行读取操作而不执行任何插入、更新或删除操作。 元数据注释 虽然不直接影响行为但可以在表上添加注释说明这是一个只读外部表作为一种提醒。 2、
import方式 需要注意 这种方式不需要指定表结构但是外部表名需要一致也就是当前 PostgreSQL 的 SCHEMA 下不能存在同名表否则创建失败。从 PostgreSQL 9.5 开始支持 IMPORT FOREIGN SCHEMA 为 Oracle 模式中的所有表批量导入表定义。IMPORT FOREIGN SCHEMA 将为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图但不包括同义词。Oracle SCHEMA 名称通常为大写。由于 PostgreSQL 在处理之前将名称转换为小写因此您必须用双引号保护 SCHEMA 名称例如SCOTT。LIMIT TO 导入括号内包含的表多个表以逗号分隔EXCEPT 导入不包含(排除)括号内的表多个表以逗号分隔 -- 导入单表 IMPORT FOREIGN SCHEMA SCOTT limit to (EMP) from server oradb into public; -- 导入多表 IMPORT FOREIGN SCHEMA SCOTT LIMIT TO (EMP,DEPT) from server oradb into public OPTIONS (readonly true, prefetch 100); -- 排除表导入 IMPORT FOREIGN SCHEMA SCOTT EXCEPT (EMP,DEPT) from server oradb into public; -- IMPORT FOREIGN SCHEMA 支持的选项 -- case(默认smart)控制导入期间表名和列名的大小写参数值 -- keep: 保留 Oracle 中的名称通常为大写。 -- lower: 将所有表名和列名转换为小写。 -- smart: 仅转换 Oracle 中全部大写的名称。 -- collation(默认default)用于 case 选项的 lower 和 smart 选项的排序规则 -- dblink -- readonly -- max_long -- sample_percent -- prefetch 例如ZSK用户下的五张表都导入到postgresql数据库下的public 这个模式下
IMPORT FOREIGN SCHEMA ZSK from server oradb into public; 导入单张表
IMPORT FOREIGN SCHEMA ZSK limit to (employees) from server oradb into public OPTIONS (readonly true );可以看到导入比较方便字段值转换什么的都给搞好了 删除外部表
drop FOREIGN TABLE employees
