oracle与mysql跨库连接_PostgreSQL+Oracle跨库连接实操
在我们异构数据库数据迁移的过程中，涉及大量的跨库查询操作的需求。常见的跨库连接有A连B，B连B，B连A的现实需求。本文带给大家的是PostgreSQL和oracle之间上述三种跨库连接的实战分享。
[PostgreSQL连接Oracle]
使用Oracle_FDW实现Postgres连接Oracle
Oracle_fdw的编译依赖系统中需要有pg_config和Oracle的环境,需要安装oracle客户端。
1、安装客户端需要如下3个文件包unzipinstantclient-basic-linux.x64-12.2.0.1.0.zip
unzipinstantclient-sdk-linux.x64-12.2.0.1.0.zip
unzipinstantclient-sqlplus-linux.x64-12.2.0.1.0.zip
2、配置环境变量exportORACLE_HOME=/opt/oracle/instantclient
exportOCI_LIB_DIR=$ORACLE_HOME
exportOCI_INC_DIR=$ORACLE_HOME/sdk/include
exportLD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
3、下载oracle_fdw插件并安装unziporacle_fdw-2.0.0.zip
cdoracle_fdw-2.0.0
Make
Makeinstall
--检查确认没有依赖未解决
lddoracle_fdw.so
4、创建拓展postgres=#create extension oracle_fdw ;
postgres=#\des
Listof foreign servers
Name | Owner | Foreign-data wrapper
-------+----------+----------------------
oradb| postgres | oracle_fdw
5、创建外部数据源服务postgres=#CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver '//192.168.217.120:1521/posdb');
6、建用户映射postgres=#CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'wen',password 'wen123');
7、创建外部表postgres=#CREATE FOREIGN TABLE "vol_audit_nbr_sum_qs"(
idint,
uuidcharacter(32),
namecharacter(32),
Minfotext) SERVER oradb OPTIONS (table 'vol_audit_nbr_sum_qs');
8、测试访问速度
测试结果：
1、使用postgres连接oracle查询2G的表，4520万行数据，全表扫描耗时9分钟23秒。select* from vol_audit_nbr_sum_qs;
Time:563775.627 ms (09:23.776)
2、建表时加入prefetch'10240' 参数后耗时5分22秒。select* from vol_audit_nbr_sum_qs;
Time:322470.280 ms (05:22.470)
[PostgreSQL连接PostgreSQL]
使用Postgres_FDW实现Postgresql连接Postgresql
Postgres_FDW为postgresql自带拓展可以直接创建。支持远程select和DML，和本地表操作一样。
1、创建拓展postgres=#create extension postgres_fdw;
2、创建服务postgres=#create server f_postgres foreign data wrapper postgres_fdw options(host '192.168.86.95',port '5433',dbname 'db_demo');
3、创建用户映射postgres=#create user mapping if not exists for postgres server f_postgresoptions (user 'wen',password 'wen123');
4、创建外部表postgres=#CREATE FOREIGN TABLE "vol_audit_nbr_sum_qs"(
idint,
uuidcharacter(32),
namecharacter(32),
Minfotext) SERVER f_postgres OPTIONS (table_name'vol_audit_nbr_sum_qs');
5、测试访问速度
测试结果：
1、使用postgres连接postgres查询2G的表，4520万行数据，全表扫描耗时12分钟59秒。select* from vol_audit_nbr_sum_qs;
Time:779932.685 ms (12:59.933)
2、建表时加入fetch_size'10240' 参数后耗时3分58秒。select* from vol_audit_nbr_sum_qs;
Time:238503.016 ms (03:58.503)
[Oracle连接PostgreSQL]
1、安装postgresql的odbc驱动包--需要安装unixODBC和postgresql_odbc
yuminstall -y unixODBC.x86_64
yuminstall -y postgresql-odbc.x86_64
2、配置/etc/odbc.ini[postgresql]
Description= PostgresSQLODBC
Driver= PostgreSQL
Database= testdb
Servername= 192.168.12.123
UserName= test
Password= test123
Port= 5432
ReadOnly= 0
ConnSettings= set client_encoding to UTF8
连接成功：
[root@localoracle~]# isql postgresql
+---------------------------------------+
|Connected! |
| |
|sql-statement |
|help [tablename] |
|quit |
| |
+---------------------------------------+
3、创建.odbc.ini文件
在/home/oracle下创建隐藏文件.odbc.ini[PG_LINK]
Description = PostgreSQL connection to SallyDB
Driver = usr/lib64/psqlodbc.so
Setup = usr/lib64/libodbcpsqlS.so
Database = testdb
Servername = 192.168.12.123
UserName = test
Password = test123
Port = 5432
Protocol = 12.2
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings = set client_encoding to UTF8
4、配置透明网关
在$ORACLE_HOME/network/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字HS_FDS_CONNECT_INFO= PG_LINK
HS_FDS_TRACE_LEVEL= 255
HS_FDS_SHAREABLE_NAME=/usr/lib64/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
setODBCINI=/home/oracle/.odbc.ini
5、配置tnsnames.ora文件PG_LINK =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.120)(PORT=1521))
(CONNECT_DATA=(SID=PG_LINK))
(HS=OK)
)
6、配置监听文件SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PG_LINK)
(ORACLE_HOME=/u01/app/oracle/product/12.2/db_1) (ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/12.2/db_1/bin/") (PROGRAM=dg4odbc)
)
)
7、创建DBLINKcreate database link to_pglink connect to "test" identified by"test123" using 'PG_LINK';
8、访问PG数据库
访问postgre的数据库表是需要表名字小写并加上双引号selectcount(*) from "vol_audit_nbr_sum_qs"@to_pglink;
9、测试访问速度
测试结果：
使用oracle连接postgres查询2G的表，4520万行数据，全表扫描耗时10分钟37秒。SQL>set timing on
SQL>set autot trace
SQL>select "billing_cycle_id" from"vol_audit_nbr_sum_qs"@to_pglink;
45201535rows selected.
Elapsed:00:10:37.14