On Apr 30, 5:12 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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 seehttp://asktom.oracle.com/tkyte/flat/index.htmlwhich would be
> the FAQ for this question.
>
> jg
Good point of course was thinking since OP had tested datapump that he
had done some comparisons against sqlplus but ya never know ...
Received on Mon Apr 30 2007 - 16:19:35 CDT
