1

I want to use sqlplus with spool to export about 14 million rows into '.csv' file. I'm running the script on windows platform.

The script file looks like the following:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';
set colsep ,
set headsep off
set pagesize 0
set trimspool on
set head on
set feed off
set term off
set verify off
spool ./Ora_Data/&1..csv
select * from &1;
spool off
exit;

Then, I use this command sqlplus -s -m "CSV ON DELIM ',' QUOTE ON" user/pwd@host/sid '@sql_to_csv.sql' 'table1'

Everything is right until the 13th million line. I found that this line is also the headers.

The csv content looks like the following:

header1,header2,header3
1,2,3
4,5,6
...
header1,header2,header3
7,8,9
...

I just want the headers occur at the first line.

header1,header2,header3
1,2,3
4,5,6
7,8,9
...

Why does the header appear multiple times? How to resolve it?

Xie Steven
  • 8,544
  • 1
  • 9
  • 23
  • No idea; SET PAGESIZE should take care about it. If it is one-time spool, huh - delete that line and forget about it. Otherwise, wait for someone who actually knows what they are saying. – Littlefoot Jun 30 '23 at 10:09

0 Answers0