Re: Fastest way to ASCII file or other ideas?

On Apr 30, 1:51 pm, EscVector <J..._at_webthere.com> wrote:
> On Apr 30, 4:35 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> > On Apr 30, 4:07 pm, EscVector <J..._at_webthere.com> wrote:
>
> > > I'm looking for suggestions on the fastest way to dump 100+ Million
> > > rows from Oracle to an ASCII file.
> > > We've looked at external table with data pump. This works with speed,
> > > but that format is proprietary. We've also looked at UTL_FILE and
> > > this is slow. Looking for ideas suggestions. Streams possibly? Any
> > > third party freeware?
>
> > Traditionally for best speed one tactic is a c program running on same
> > server as database use bequeath or ipc type connection.
>
> Great suggestion. Thought of this but they didn't want to go with C
> or C++. Will explore concurrency/threading issues and maybe I can
> convince that it's not so bad.... I've only ever created single
> threaded C processes. What to do if C/native can't be used?

I've found (at least on unix servers) this sort of thing works quite fast:

sqlplus -s << EOF > someoutputfile
$schema/$password
-- passed into script so not left around for prying eyes set colsep |
set verify off
set echo off
set feedback off
set pages 0
set heading off
set termout off
set linesize 120
select
etc, etc
from
etc
;
EOF Also note you can do things like
echo "$schemapassword
select whatever
from whereever
;" |sqlplus -s|somethingelse.awk > outputfile

Since you are talking millions of rows, things like sqlplus process startup are the least of your worries. You just don't want to be dealing with networks.

Also see http://asktom.oracle.com/tkyte/flat/index.html which would be the FAQ for this question.

jg

--
@home.com is bogus.
"There are many reasons it can take a long time to log into a
database. Sometimes the solution to the problem can be as simple as
switching to decaf coffee, while others might require a total
Application rewrite." - Ken Reardon