Re: Extracting large volumes of data from oracle to flat files - Poor Performance

"mark" <mark_knisely_at_yahoo.com> wrote in message news:8d852b26.0111211025.62ca1e49_at_posting.google.com...
> We are working on a data mart and we need to extract large
> volumes of transactional data from the source system (also
> Oracle DB) to flat files. We then use SQL Loader with the
> direct option to load the data into our staging tables. While
> the Load performs very well (less than 10 minutes to load 4
> million records), the extract is Extremely slow. The method we
> are currently using for the extract is an sql script (SQL Plus)
> spooling to a comma separated flat file. For 4 million records,
> the process is taking ~4+ hours to complete. We moved to a dual
> processor machine and that didn't make any difference - in fact,
> both CPU remain over 90% idle while the extract is running. Is
> there a more effective than SQL Plus to extact data to a flat
> file?

I fail to understand why you are using flat files if you can use a database link and sql*plus COPY to accomplish this.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address