I have a query that hits multiple tables in an Oracle database (11g). I don't have access to the server itself. I use SQL Developer or SQL*Plus to connect.

There are about 31 million rows, and 7 columns, and I want to dump it into a CSV file.

How can I achieve this?

  • my recommendation - install oracle express on your pc, export the data from the unprivileged machine and import into your local environment.
    – haki
    Commented May 3, 2013 at 19:58
  • 4
    What are you going to use the csv for? In other words, what are your goals for the data? Commented May 3, 2013 at 23:15

2 Answers 2


Asuming this is to transport data to an other system. It that case this will work:

set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off

If you don't want a header line, change to heading off

If this is to analyze the data in excel, please wake up and learn sql. SQL, especially Oracle, has many great tools to help analyzing your data. By the time the data is transported into the csv file, Oracle analytics has already done some great analysis.Data Warehousing and Business Intelligence but for many tasks plain SQL can be good enough. Start reading about Oracle Analytics.

  • 2
    I'm not a fan of these abbreviated SQL*Plus formatting commands -- PAGESIZE and PAGES sound like they mean different things to me, although the latter is just an abbreviation for the former. PAGESIZE 9999 and HEADING ON don't sound like good choices. Commented May 4, 2013 at 9:44
  • @David-Aldridge yes, you are right, would be nice to be able to dump just 1 header instead of for every page. Max pages size 50000 rows still gives lots of headers.
    – user953
    Commented May 4, 2013 at 10:18
  • 1
    Or no headers at all ... pagesize 0? Commented May 4, 2013 at 12:03
  • will this be faster than doing from localhost SQL developer?
    – Lav Patel
    Commented Feb 19, 2019 at 15:35
  • sqlplus is a lighter tool than SQL developer .... maybe the speed won't differ a lot but I think sqlplus is friendlier if you are on a busy system.
    – user953
    Commented Feb 19, 2019 at 16:12

I'd look at writing a query to produce the comma separated values, taking care to string out any extraneous carriage returns or other funnies that would break the format, especially if you have columns that are free-form text entry and might have all sorts of entries in them. I once saw data for "email_content" that had an example of comma-separated values pasted into it. That was fun.

Your code would end up along the lines of ....

  to_char(my_date,'YYYY-MM-DD HH24:MI:SS')
  ||','  ||to_char(my_number,'fm999999999999990.00000')
  ||',"' ||regexp_replace(my_string,'[[:cntrl]]','')
  ||'",' ||to_char(my_number,'fm999999999999990.00000')

Then either use SQL*Plus to spool the output, using the commands shown in the docs to suppress headings, set a suitable line size, suppress feedback of the number of rows, increase the default arraysize to around 100 perhaps, or use UTL_File to spool it out as a server process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.