Skip to Main Content
  • Questions
  • How to unload table data to csv file in old oracle version, the fastest way

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, andy.

Asked: December 18, 2018 - 2:50 pm UTC

Last updated: December 19, 2018 - 4:16 am UTC

Version: 8i

Viewed 1000+ times

You Asked

We are currently using Oracle Database 8i.
We have multiples table and around 600 millions of rows.
We use SQLPLUS to export rows to csv with nested queries.

Configuration:
set term off
set feedback off
set linesize 32767
set hea off
set pagesize 0
set spa 0
set verify off
set TRIMSPOOL ON
set trims on


Currently, the CSV export takes 1 week.

We found another topic suggesting to use parallel spool feature but we are using old Oracle Database version and parallel features seems not to be available.

What is the best way to reduce processing time ? Using ETL could be a good solution ?

Thank you

and Connor said...

A week ?!?!?!?! Holy moly.

One very important thing you've missed is:

set arraysize 1000

Having said that, SQL Plus obviously has overheads in that it is not purely a file unloading solution. You could use a small pro*c program to get a much faster file unload. You can get that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=yet-another-ascii-dump-issue

You pass in an SQL and it will unload it.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

set flush off

lh, December 19, 2018 - 12:00 pm UTC

set flush off
Arraysize increase will decrease the benefit of this.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database