Re: Query results to .csv

If both servers are on the same network with reasonable connection speed,

• create target_table nologging as select * from source_table_at_source_db

will beat all other options as it creates the table in one step - no writing/transfering/reading intermediate files in between, and nologging uses the same direct-path functionality as SQL*Loader. Simple enough to whip out the stopwatch and test whether it applies in your case or not.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, August 26, 2003 8:04 AM

> Thanks Tanel
> We will undoubtedly use export/import for the many small tables. We are
> looking for alternatives that will perform even faster. The insert phase
> seems to be the slowest part, and that is where SQL*Loader in direct path
> really shines. Now the next issue is how to produce a CSV file as fast as
> possible, and so far it looks like Jared's Perl program is the clear
winner.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Monday, August 25, 2003 1:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi!
>
> Spooling from sqlplus is VERY slow.
> Is the source database Oracle? Then use export/import
> If not, is there an ODBC driver for source database? Then use Oracle
> heterogenous services and do your transfer directly, without any
> intermediate files.
> Or use some very expensive software for doing this simple job...
>
> Tanel.
> P.S. if you definitely want to spool to textfile fast, Sparky could be
what
> you want...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, August 25, 2003 7:24 PM
>
>
> > Jared - Thanks for posting this. At the moment, we are preparing to move
> > large database to a new server. Based on the advice you posted several
> > months ago, we have been testing SQL*Loader and as you predicted, it is
> > indeed fast. But also as you predicted, using SQL*Plus to create a CSV
> isn't
> > very fast. Am I correct in assuming the dump.sql will not be the best
> choice
> > for large tables? We are installing perl since you mentioned that would
> > probably be much faster.
> >
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Monday, August 25, 2003 9:40 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > http://www.cybcon.com/~jkstill/util/dump/dump.html
> >
> > On Mon, 2003-08-25 at 05:39, Imran Ashraf wrote:
> > > Hi,
> > >
> > > Whats the best way to write the results of a SQL query to a CSV file?
> > >
> > > Thanks.
> > >
> > > _________________________________________________________________
> > > Hotmail messages direct to your mobile phone
> > http://www.msn.co.uk/msnmobile
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Imran Ashraf
> > > INET: imran9a_at_hotmail.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.COM
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).