重庆思庄oracle技术分享-oracle11g 通过DBLINK访问PG13 中数据

duanweifang

于 2023-03-26 09:52:39 发布

阅读量286

点赞数

文章标签： oracle 数据库 postgresql

原文链接：http://bbs.cqsztech.com/forum.php?mod=viewthread&tid=30573&extra=page%3D1

版权

具体步骤如下：

1.安装 unixODBC,postgresql13-odbc
root用户执行：

yum install -y https://download.postgresql.org/ ... o-latest.noarch.rpm
yum install -y unixODBC.x86_64
yum - y install postgresql13-odbc.x86_64
unlink /usr/lib64/libpq.so.5
ln -s /usr/pgsql-13/lib/libpq.so.5.13 /usr/lib64/libpq.so.5

2.修改文件/etc/odbcinst.ini，将Driver改成pg13的

[PostgreSQL]
Description    = ODBC for PostgreSQL
Driver       = /usr/pgsql-13/lib/psqlodbcw.so
Setup          = /usr/lib/libodbcpsqlS.so
Driver64       = /usr/pgsql-13/lib/psqlodbcw.so
Setup64       = /usr/lib64/libodbcpsqlS.so
FileUsage    = 1

3.创建文件/etc/odbc.ini

[PG]
Description       = PostgreSQL connection to SallyDB
Driver             = PostgreSQL
Database          =zltj
Servername       =1.1.8.96
UserName          =postgres
Password          =zltj2021
Port             = 5432
UseDeclareFetch    = 1
CommLog          = /tmp/pgodbclink.log
Protocol          =12.8
ReadOnly          =No
RowVersioning    =yes
ShowSystemTables =YES
ShowOidColumn    =yes
FakeOidIndex       =yes
ConnSettings = set client_encoding to gbk
Debug = 0
LowerCaseIdentifier = 1

4.测试连接：

isql -v pg
+---------------------------------------+
| Connected!                         |
|                                     |
| sql-statement                      |
| help [tablename]                   |
| quit                               |
|                                     |
+---------------------------------------+
SQL>help    -- 会把POSTGRES用户下的所有表列出来

oracle用户执行下面的步骤：

创建文件~/.odbc.ini

5.创建监听
  在文件 /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 中追加：

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC=
   (SID_NAME=PG)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (ENV="LD_LIBRARY_PATH=/usr/pgsql-13/lib/:/lib64:/usr/lib64/:/u01/app/oracle/product/11.2.0/db_1/lib")
      (PROGRAM=dg4odbc)
)
  )

6.配置tnsnames.ora文件
在文件 /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 中追加如下内容：

PG =
  (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hisdb01)(PORT=1521))
(CONNECT_DATA=(SID=PG))
(HS=OK)
  )

7.配置透明网关
创建文件： /u01/app/oracle/product/11.2.0/db_1/hs/admin/initPG.ora

HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-13/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk
HS_KEEP_REMTE_COLUMN_SIZE=ALL
HS_FDS_DEFAULT_SCHEMA_NAME = public
set ODBCINI=/etc/odbc.ini

8.创建dblink

create public database link to_pglink connect to "postgres" identified by "zltj2021" using 'PG';

9.使用DBLINK连接访问PG

select count(*) from "drcs"@to_pglink;
  COUNT(*)
----------
   3079

10.使用同义词访问：

create or replace synonym drcs  for "drcs"@to_pglink;
select count(*) from drcs;