From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Oracle data to PG
Date: Thu, 28 Jul 2022 08:42:41 -0500
Message-ID: <1add0fb2-20fc-f3c4-9092-07780811b945@gmail.com> (raw)
In-Reply-To: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de>
References: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de>
On 7/28/22 03:55, Dischner, Anton wrote:
>
> Hi Team,
>
> we are searching for a way to import a dump from an Oracle Installation
> into PostgreSQL.
>
> Ist not administrated by us, so foreing table or connect as an user (as
> ora2pg seems to need) is not an option.
>
> We had a look into
> https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-co...
>
> especially:
>
> * Ora2pg <http://ora2pg.darold.net/>—a robust migration tool that
> connects to an Oracle database, extracts schemas and tables and
> generates SQL scripts that can be loaded into Postgres.
> * Ora_migrator—an extension that uses an oracle_fdw foreign data
> wrapper to extract data from an Oracle database.
> * Orafce—this extension allows you to implement a number of Oracle
> functions in Postgres. It also provides support for Oracle date
> formatting and additional Oracle data types.
> * EDB Migration Portal
> <https://www.enterprisedb.com/products/free-oracle-postgres-migration-tool>—a
> web-based service for migrating from Oracle to EDB Postgres Advanced
> Server
> <https://www.enterprisedb.com/products/edb-postgres-advanced-server-secure-ha-oracle-compatible;
> that features Assessment, Schema conversion and Compatibility reporting.
>
> we have to import data only.
>
> No indexes, PL/SQL or other stuff.
>
> Maybe it makes sense to install an Oracle Instance, import the data and
> then connect as a user and do the conversion.
>
> What are you hints and experiences to plan and accomplish this task?
>
We successfully used ora2pg to extract 5TB of LOB-heavy tables out of
Oracle. Each SQL files generated was a bunch of COPY statements in stdin
format.
It's multi-process, can extract from views, and tunable based on the size of
the LOBs.
--
Angular momentum makes the world go 'round.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Oracle data to PG
In-Reply-To: <1add0fb2-20fc-f3c4-9092-07780811b945@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox