Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E5B139FB2DC for ; Fri, 9 Mar 2007 14:40:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 91948-02 for ; Fri, 9 Mar 2007 14:40:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc3-s39.bay0.hotmail.com (bay0-omc3-s39.bay0.hotmail.com [65.54.246.239]) by postgresql.org (Postfix) with ESMTP id 4B16B9FA34F for ; Fri, 9 Mar 2007 14:40:03 -0400 (AST) Received: from hotmail.com ([65.55.138.15]) by bay0-omc3-s39.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Fri, 9 Mar 2007 10:40:02 -0800 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 9 Mar 2007 10:40:02 -0800 Message-ID: Received: from 209.6.18.70 by BAY133-DAV5.phx.gbl with DAV; Fri, 09 Mar 2007 18:40:01 +0000 X-Originating-IP: [209.6.18.70] X-Originating-Email: [mgainty@hotmail.com] X-Sender: mgainty@hotmail.com Reply-To: "Martin Gainty" From: "Martin Gainty" To: "Ezequias Rodrigues da Rocha" , "pgsql general" References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> Subject: Re: PostgreSQL to Oracle Date: Fri, 9 Mar 2007 13:40:00 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007C_01C76250.6F1C2650" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 09 Mar 2007 18:40:02.0209 (UTC) FILETIME=[59009110:01C7627A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.241 tagged_above=0 required=5 tests=AWL, BAYES_00, HTML_40_50, HTML_MESSAGE, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/548 X-Sequence-Number: 110738 This is a multi-part message in MIME format. ------=_NextPart_000_007C_01C76250.6F1C2650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Ezequias- first you will need to pg_dump everything.. schema..tables..functions = then your Data into a text format which has no whitespace characters There are 2 bulk loaders available from Oracle 1)Brand new DataPump 2)Tried and true sqlldr (which I recommend) I would highly recommend reading and understanding the Extract / = Transform /Load document located at http://www.oracle.com/technology/obe/obe10gdb/bidw/etl2/etl2.htm Also triple check the extents you are allocating for the tablespace for = tables to be inserted into that tablespace will guaranteed to be = sufficient size (If you dont know the size of a block ask..as this isnt something you = can fix later..unless you want to spend a 3day weekend doing a = rollback..) Become familiar with LOAD DATA INFILE command links available from = either Stanford (or Oracle) http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html Be mindful that Oracle DB is used by ALL of the major players so inserting nulls which could violate any constraint (constraints being = unique or foreign key) are forbidden In fact inserting nulls into any column is discouraged as it may violate = the relationship of dependent table records to primary table If you have time and or diskspace constraint you may want to look at = Direct Path Loading which creates preformatted data blocks http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dl= ins.htm Caveat emptor with this option as the logger is turned off and if there = are any burps along the way you wont know which record it burped on Feel free to ping me if you have any questions/ Saludos Cordiales, Martin -- -------------------------------------------------------------------------= --=20 This e-mail message (including attachments, if any) is intended for the = use of the individual or entity to which it is addressed and may contain = information that is privileged, proprietary , confidential and exempt = from disclosure. If you are not the intended recipient, you are notified = that any dissemination, distribution or copying of this communication is = strictly prohibited. -------------------------------------------------------------------------= --=20 Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y sont = annex=E9es, le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et = peut contenir des renseignements de caract=E8re priv=E9 ou confidentiel. = Si vous n'=EAtes pas le destinataire de ce document, nous vous signalons = qu'il est strictement interdit de le diffuser, de le distribuer ou de le = reproduire. ----- Original Message -----=20 From: Ezequias Rodrigues da Rocha=20 To: pgsql general ; PostgreSQL=20 Sent: Friday, March 09, 2007 12:54 PM Subject: [GENERAL] PostgreSQL to Oracle Hi list, Is it a simple action to convert a database from PostgreSQL to Oracle = ? I mean a simple database with=20 33 tables 8 functions 31 sequencies 2 triggers 1 type 3 views Has someone any idea ?=20 --=20 Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ------=_NextPart_000_007C_01C76250.6F1C2650 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Ezequias-

first you will need to = pg_dump=20 everything.. schema..tables..functions then your Data into a text format = which=20 has no whitespace characters
There are 2 bulk loaders available from = Oracle
1)Brand new DataPump
2)Tried = and true=20 sqlldr (which I recommend)
I would = highly=20 recommend reading and understanding the Extract / Transform /Load = document=20 located at
http://www.oracle.com/technology/obe/obe10gdb/bidw/etl2/etl2.htm
Also triple check the extents you are = allocating for the=20 tablespace for tables to be inserted into that tablespace = will=20 guaranteed to be sufficient size
(If you dont know the size of a = block=20 ask..as this isnt something you can fix later..unless you want to spend = a 3day=20 weekend doing a rollback..)
Become familiar with LOAD DATA INFILE = command=20 links available from either Stanford (or = Oracle)
htt= p://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html
Be mindful that Oracle DB is used by = ALL of the=20 major players
so inserting nulls which could violate = any=20 constraint (constraints being unique or foreign key) are = forbidden
In fact inserting nulls into any column = is=20 discouraged as it may violate the relationship of dependent table = records to=20 primary table
If you have = time and or=20 diskspace constraint you may want to look at Direct Path = Loading which=20 creates preformatted data blocks
http://download-west.oracle.com/docs/cd/B10501_01/server= 920/a96524/c21dlins.htm
Caveat emptor with this = option as the=20 logger is turned off and if there are any burps along the way you = wont=20 know which record it burped on

Feel free to ping me if you have any = questions/

Saludos=20 Cordiales,
Martin --
--------------------------------------------------------------------= -------=20
This e-mail message (including attachments, if any) is intended for = the use=20 of the individual or entity to which it is addressed and may contain = information=20 that is privileged, proprietary , confidential and exempt from = disclosure. If=20 you are not the intended recipient, you are notified that any = dissemination,=20 distribution or copying of this communication is strictly=20 prohibited.
----------------------------------------------------------= -----------------=20
Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y = sont annex=E9es,=20 le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et peut contenir = des=20 renseignements de caract=E8re priv=E9 ou confidentiel. Si vous n'=EAtes = pas le=20 destinataire de ce document, nous vous signalons qu'il est strictement = interdit=20 de le diffuser, de le distribuer ou de le reproduire.
----- Original Message -----
From:=20 Ezequias Rodrigues da = Rocha
To: pgsql general ; PostgreSQL
Sent: Friday, March 09, 2007 = 12:54=20 PM
Subject: [GENERAL] PostgreSQL = to=20 Oracle

Hi list,

Is it a simple action to convert a = database=20 from PostgreSQL to Oracle ?

I mean a simple database with =

33=20 tables
8 functions
31 sequencies
2 triggers
1 type
3=20 views

Has someone any idea ?

-- =
Ezequias=20 Rodrigues da Rocha
http://ezequiasrocha.blogspot= com/
use=20 Mozilla Firefox:http://br.mozdev.org/firefox/=20 ------=_NextPart_000_007C_01C76250.6F1C2650--