1

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?

James Z
  • 12,209
  • 10
  • 24
  • 44
user1830049
  • 109
  • 1
  • 6
  • 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 Mar 23 '17 at 16:17
  • In addition to what Alex has asked, how are you generating the csv in sqlplus? Are you doing `select col1||','||col2||','||.... `? – Boneist Mar 23 '17 at 16:37
  • 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 Mar 23 '17 at 16:42
  • You have not specified the version of Oracle. But if you are using 12.2, then simply use the CSV Markuo. – BobC Mar 23 '17 at 16:59
  • show us HOW you're doing the csv in both tools, otherwise we have to guess – thatjeffsmith Mar 23 '17 at 17:17

1 Answers1

0

Add the following at the starting of your script

set term off
set feed off
set sqlformat csv
set heading off
Jain26
  • 63
  • 1
  • 7