Re: [PERFORM] Best practice to load a huge table from ORACLE to PG</= TITLE> </HEAD> <BODY> Yep – just do something like this within sqlplus (from <a href= =3D"http://www.dbforums.com/showthread.php?t=3D350614):">http://www.dbforums.com= /showthread.php?t=3D350614):</a> set termout off set hea off set pagesize 0 spool c:\whatever.csv select a.a||','||a.b||','||a.c from a where a.a=3D"whatever"; spool off COPY is the fastest approach to get it into PG. - Luke On 4/26/08 6:25 AM, "Adonias Malosso" <malosso@gmail.com> w= rote: <BLOCKQUOTE>Hi All, I´d like to know what´s the best practice to LOAD a 70 milion r= ows, 101 columns table from ORACLE to PGSQL. The current approach is to dump the data in CSV and than COPY it to Postgre= sql. Anyone has a better idea. Regards Adonias Malosso </BLOCKQUOTE> </BODY> </HTML> --B_3292046015

Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 7351A2E0070 for ; Sat, 26 Apr 2008 13:14:03 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81370-05 for ; Sat, 26 Apr 2008 13:13:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exprod6og107.obsmtp.com (exprod6og107.obsmtp.com [64.18.1.208]) by developer.postgresql.org (Postfix) with SMTP id 252172E0033 for ; Sat, 26 Apr 2008 13:13:56 -0300 (ADT) Received: from source ([63.240.6.3]) (using TLSv1) by exprod6ob107.postini.com ([64.18.5.12]) with SMTP; Sat, 26 Apr 2008 09:13:55 PDT Received: from d01smtp07.Mi8.com ([172.16.1.114]) by Outbound02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 26 Apr 2008 12:16:27 -0400 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.165]) by d01smtp07.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 26 Apr 2008 12:16:26 -0400 Received: from 69.181.101.67 ([69.181.101.67]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ([172.16.1.107]) with Microsoft Exchange Server HTTP-DAV ; Sat, 26 Apr 2008 16:13:35 +0000 User-Agent: Microsoft-Entourage/12.1.0.080305 Date: Sat, 26 Apr 2008 09:13:34 -0700 Subject: Re: Best practice to load a huge table from ORACLE to PG From: Luke Lonergan To: Adonias Malosso , Message-ID: Thread-Topic: [PERFORM] Best practice to load a huge table from ORACLE to PG Thread-Index: AcinoQoa9uChHdfAQL+ugD2/mF94nAAF2++b In-Reply-To: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com> Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3292046015_67575" X-OriginalArrivalTime: 26 Apr 2008 16:16:26.0951 (UTC) FILETIME=[E0ED0570:01C8A7B8] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL=-0.365, HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819, RCVD_IN_DNSWL_MED=-4, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: X-Archive-Number: 200804/375 X-Sequence-Number: 29868 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3292046015_67575 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Yep =AD just do something like this within sqlplus (from http://www.dbforums.com/showthread.php?t=3D350614): set termout off set hea off set pagesize 0 spool c:\whatever.csv select a.a||','||a.b||','||a.c from a where a.a=3D"whatever"; spool off COPY is the fastest approach to get it into PG. - Luke On 4/26/08 6:25 AM, "Adonias Malosso" wrote: > Hi All, >=20 > I=B4d like to know what=B4s the best practice to LOAD a 70 milion rows, 101 > columns table=20 > from ORACLE to PGSQL. >=20 > The current approach is to dump the data in CSV and than COPY it to > Postgresql. >=20 > Anyone has a better idea. >=20 >=20 > Regards > Adonias Malosso >=20 --B_3292046015_67575 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] Best practice to load a huge table from ORACLE to PG</= TITLE> </HEAD> <BODY> Yep – just do something like this within sqlplus (from <a href= =3D"http://www.dbforums.com/showthread.php?t=3D350614):">http://www.dbforums.com= /showthread.php?t=3D350614):</a> set termout off set hea off set pagesize 0 spool c:\whatever.csv select a.a||','||a.b||','||a.c from a where a.a=3D"whatever"; spool off COPY is the fastest approach to get it into PG. - Luke On 4/26/08 6:25 AM, "Adonias Malosso" <malosso@gmail.com> w= rote: <BLOCKQUOTE>Hi All, I´d like to know what´s the best practice to LOAD a 70 milion r= ows, 101 columns table from ORACLE to PGSQL. The current approach is to dump the data in CSV and than COPY it to Postgre= sql. Anyone has a better idea. Regards Adonias Malosso </BLOCKQUOTE> </BODY> </HTML> --B_3292046015_67575--