1
votes

I'm running a query which results 100 000 records/rows of data. Using Spool command in sqlplus I generate a csv which stores all the output of the query.

The csv file with 100 000 records gives me a size of 2.5 GB approximately.

The same export option I'm doing it in sql developer which produces a file with size of 50 MB approximately.

How to reduce filesize during export using spool command?

1
They produce the same number of rows in the spooled files? ('export' means something specific in Oracle... are you actually spooling from SQL Developer, or exporting from the right-clck menu in the data grid?). Is the output data format the same in both? Have you done set trimspool on in SQL*Plus, and if not, does that make any difference? - Alex Poole
In addition to what Alex has asked, how are you generating the csv in sqlplus? Are you doing select col1||','||col2||','||.... ? - Boneist
Thats what I meant by 'output data format', but yes, that's clearer. The export format from SQL Developer (if not using spool) is also relevant. The question might really be 'how do I spool as CSV from SQL*Plus'. Wonder if colsep` is being used instead of concatenation, which would waste a lot of space? - Alex Poole
You have not specified the version of Oracle. But if you are using 12.2, then simply use the CSV Markuo. - BobC
show us HOW you're doing the csv in both tools, otherwise we have to guess - thatjeffsmith

1 Answers

0
votes

Add the following at the starting of your script

set term off
set feed off
set sqlformat csv
set heading off