Stack Overflow
Stack Overflow

Questions tagged [oracle-fdw]

A foreign data wrapper for PostgreSQL to access Oracle tables as if they were local PostgreSQL tables.

A for to access tables as if they were local PostgreSQL tables.

Use this tag when you have questions about installations or usage of oracle_fdw.

Resources:

51 questions
2
votes
1 answer

Identify the postgres extension version shows different versions

Have a postgres database with extensions If I do psql>\dx it shows oracle_fdw, 1.1, foreign data wrapper for Oracle access But if I do psql>SELECT oracle_diag('oraclegwserver'); it says oracle_fdw 2.0.0, PostgreSQL 10.0, Oracle client 11.2.0.3.0,…
Vik
  • 333
  • 5
  • 14
2
votes
1 answer

Reference on Foreign Table Postgres 9.5

I am using oracle_fdw to connect from a postgres 9.5.3 to an Oracle database. The server and user mappings have been created on the postgres db: CREATE FOREIGN SERVER foo FOREIGN DATA WRAPPER oracle_fdw OPTIONS (connection info..); GRANT USAGE…
Jakob
  • 214
  • 1
  • 2
  • 15
1
vote
1 answer

Postgres Foreign Data Wrapper: insert remote primary key sequence increment issue. Version Problem?

There is a later version where we are creating a foreign table and in the option field we right "table" instead of "table_name"? In my work, we have an old oracle database with all alphanumeric information. To join alphanumeric information with…
Joao Folgado
  • 11
  • 1
1
vote
1 answer

Question. oracle_fdw - Import foreign schema limit to synonym?

oracle_fdw - ‎Is it possible in PostgreSQL to import foreign schema with a limit to synonym - as in the attached image? ㅤ For example, this not working (limit to synonym): IMPORT FOREIGN SCHEMA "UserA" LIMIT TO (A_SYNONYM) FROM SERVER oracledb INTO…
WBGAD
  • 23
  • 4
1
vote
0 answers

Postgres - Oracle data type conversion

We have a foreign table that is connecting to Oracle. In Oracle, the columns are: ticker: VARCHAR2(5) article_id: NUMBER In Postgres, we have tried to create the article_id as INTEGER and NUMERIC, but every time we try and query we get this…
Landon Statis
  • 683
  • 2
  • 10
  • 25
1
vote
1 answer

Postgres foreign table performance

We have a query that uses both local tables and 1 foreign table, which is a materialized view in Oracle: SELECT ROW_NUMBER() OVER () AS sequence, a.ticker, m.company_name, a.alert_trigger, d.display_rec, z.analyst_email, z.senior_email, …
Landon Statis
  • 683
  • 2
  • 10
  • 25
1
vote
1 answer

PostgreSQL too slow when using oracle_fdw comparison operations in Oracle's queries

When Oracle date type is varchar2, when executing a query in PostgreSQL through oracle_fdw, querying using comparison operator >=, index cannot be used in this condition. Index cannot be used: >= to_char(now() - interval '7' day,…
seunofk
  • 11
  • 2
1
vote
1 answer

Selecting data from Postgres foreign table via oracle_fdw does not use the index on Oracle side

in short: we are facing a problem that full table scan is performed on remote Oracle DB instead of using the index. Setup: Postgres 12.3 in an enriched docker container with oracle base client, connecting to a remote Oracle DB in version 19c. The…
LWn
  • 11
  • 3
1
vote
1 answer

PostgreSQL 11 and Oracle foreign data wrapper

I have a problem with installing the Oracle foreign data wrapper for PostgreSQL_11 on CENTOS 8. I have no connection to internet, so I copied all necessary files by SSH, I have installed PostgreSQL 11 and Oracle client, and finally need to install…
Yerbol Barlybay
  • 29
  • 1
  • 3
1
vote
2 answers

Making successful use of comparison operators with postgres oracle_fdw

I am trying to build a scripting system that maintains an ongoing relationship between an Oracle DB and a Postgres DB. On one particularly large table (50B rows), efficiency in the where clauses postgres-side is obviously crucial for syncing.…
Alexi Theodore
  • 1,177
  • 10
  • 16
1
vote
2 answers

Oracle FDW support in AWS

I have an OLTP DB in Oracle and a downstream OLAP System in PostgreSQL on-premises. The data from Oracle is pumped into PostgreSQL using Oracle_FDW. I am exploring the possibility of moving the PostgreSQL to AWS, but none of the RDS have Oracle_fdw…
Ashwin Sridhar
  • 125
  • 1
  • 14
1
vote
1 answer

Is there any way to call/ execute Oracle Function via Postgres?

I have a requirement to migrate Oracle DB to Postgres, in which I am able to do so. By using FDW, I am able to access Oracle data at Postgres. Now there is a requirement to call/execute a function of Oracle via Postgres. Is there any way to do so?…
Pooja
  • 327
  • 1
  • 5
  • 20
1
vote
0 answers

Postgresql FS is full but tables aren't so big

I want to check something regarding PostgreSQL performance during my app is running. My app does the next things on 20 tables in a loop : truncate table. drop constraints on table drop indexes on table insert into local_table select * from…
JeyJ
  • 3,582
  • 4
  • 35
  • 83
1
vote
1 answer

Postgresql 9.6 Parallel execution from foreign table

I upgraded my postgresql from 9.5 to 9.6 in order to use the parallel execution to improve my performance. However, I didnt succeed to use it. In my main database almost all my selects look like : select * from foreign_table The foreign table is a…
JeyJ
  • 3,582
  • 4
  • 35
  • 83
1
vote
1 answer

improve query performance - selecting data from oracle to postgresql

I have 3 queries that are running very slow. All of the are doing the same in general : select from a view in oracle (viw oracle_fdw extension). All the views look like that : select /parallel (table_Name,4)/ column1,column2,replace(column 3,'…
JeyJ
  • 3,582
  • 4
  • 35
  • 83
1
2 3 4