Lists: | pgsql-general |
---|
From: | s <smarie(at)ekno(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | transfer data from oracle to postgres |
Date: | 2002-06-27 23:22:46 |
Message-ID: | 1025220166.3d1b9e46e2af3@www.ekno.lonelyplanet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Is there an easy way to transfer DATA from an oracle database to a
postgres database (tables are already created)? As far as I can tell
the oracle export creates a binary file. I could write a script, but
it would be a bit tedious. I imagine it has already been done?
I searched the archives and docs and the web, but the archives are
coming up quite right on my browser and I didn't find any good
suggestions.
thanks,
Sarah
smarie(at)ekno(dot)com
____________________________________________________________________________
Lonely Planet's ekno - more than a phonecard
Get ekno before you go!
http://www.ekno.lonelyplanet.com
From: | Justin Clift <justin(at)postgresql(dot)org> |
---|---|
To: | m <smarie(at)ekno(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: transfer data from oracle to postgres |
Date: | 2002-06-28 01:22:26 |
Message-ID: | 3D1BBA52.CB77638B@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi Sarah,
These is some information about converting from Oracle to PostgreSQL
here:
http://techdocs.postgresql.org/
On the front page there is a section called "Converting from other
Databases to PostgreSQL" and there is information there about converting
from Oracle to PostgreSQL.
Under the category of "Non-specific", there is a link to a conversion
guide by the pgAdmin development team which might also prove to be
useful (not sure).
Hope that helps.
:-)
Regards and best wishes,
Justin Clift
s wrote:
>
> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)? As far as I can tell
> the oracle export creates a binary file. I could write a script, but
> it would be a bit tedious. I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.
>
> thanks,
>
> Sarah
> smarie(at)ekno(dot)com
>
> ____________________________________________________________________________
> Lonely Planet's ekno - more than a phonecard
> Get ekno before you go!
> http://www.ekno.lonelyplanet.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
From: | "Graeme Merrall" <gbmerrall(at)aol(dot)com> |
---|---|
To: | "m" <smarie(at)ekno(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: transfer data from oracle to postgres |
Date: | 2002-06-28 04:34:23 |
Message-ID: | NNEKJJAGMOPDHMDJNLBJKEBOCCAA.GBMerrall@aol.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>
> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)? As far as I can tell
> the oracle export creates a binary file. I could write a script, but
> it would be a bit tedious. I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.
There's a ora2pg script in the postgres contrib directory which you can use.
A more updated versionis available from http://www.samse.fr/GPL/ora2pg/.
It's pretty good but it does not do CLOB/BLOB data and it doesn't see
synonyms either.
Cheers,
Graeme
From: | Gunther Schadow <gunther(at)aurora(dot)regenstrief(dot)org> |
---|---|
To: | m <smarie(at)ekno(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: transfer data from oracle to postgres |
Date: | 2002-07-01 23:11:10 |
Message-ID: | 3D20E18E.9030008@aurora.regenstrief.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
s wrote:
> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)? As far as I can tell
> the oracle export creates a binary file. I could write a script, but
> it would be a bit tedious. I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.
There is the ora2pg perl script. But I chose to do this manually.
It's easy to do some Oracle metadata queries and create statements
that suck the entire table. You could do that even in Oracle SQL*Plus
if it wasn't so stupid about trying to format an ASCII table, etc.
In the end the delimiter-escape problem forced me to just make a
quick Java/JDBC program. You can stream the reocrds right into a
pgsql COPY FROM command. Streaming is useful if your tables are
BIG (mine are.) I also put some counter in the middle that shows
me the progress. I noticed that PostgreSQL could pump data in as
fast as I could read it from the Oracle server. 300 to 1500 records
per second was the normal range.
regards
-Gunther
--
Gunther Schadow, M.D., Ph.D. gschadow(at)regenstrief(dot)org
Medical Information Scientist Regenstrief Institute for Health Care
Adjunct Assistant Professor Indiana University School of Medicine
tel:1(317)630-7960 http://aurora.regenstrief.org