Exporting 30 million rows to CSV

oracleoracle-sql-developer

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?

Best Answer

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.

Related Question