Re: Oracle data to PG

Mladen Gogala <gogala.mladen@xxxxxxxxx> · Fri, 29 Jul 2022 09:12:45 -0400



    On 7/28/22 04: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-convert-database-oracle-alternative
        especially:
        
          
            Ora2pg—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—a
            web-based service for migrating from Oracle to
            
              EDB Postgres Advanced Server 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?
         
        best
         
        Anton
         
         
    Hi Anton,
    Long time no see. Ora2pg can produce just the "CREATE
      TABLE/INDEX/VIEW" statements from Oracle, translated to PgSQL
      vernacular. You will have to be able to log onto the original
      Oracle instance, albeit as an application owner.

    
    Furthermore, Oracle has a free ODBC gateway which can be used to
      connect to Postgres. What you need is an Oracle listener, not an
      entire instance. Another thing that can be exceedingly useful is
      this: https://osalvador.github.io/ReplicaDB/
    You can extract Oracle data into CSV files and do COPY in PgSQL.
      I know it works with Snowflake. There is no reason for it not to
      work witg PgSQL as well. You will need some scripting, but it can
      be done.
    Be aware of some key differences: Oracle doesn't store NULL
      values in indexes. PostgreSQL does. The corollary is different
      behavior with the unique, multi-column indexes. There are many
      more index types in PgSQL, I find Bloom indexes exceptionally
      useful.
    Good luck!

    
    -- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com