We are spooling data into CSV file from oracle table at Linux server.
Using the below code:
sqlplus -s Schema_User/Schema_Password@DB_Service <<EOF
set termout off
set heading off
set underline "_"
set pagesize 0 embedded on
set linesize 32000
set LONG 50000
set colsep '"|"'
set feedback off
spool ./Temp/ABC.csv
Select COLUMN1,COLUMN2 from ABC;
spool off
EOF
The free space on the Linux server (df -h) is 500GB.
The generation of spool file ABC.csv terminates after reaching the size 500GB.
We suspect that the final size of ABC.csv will be much more than 500GB.
Please suggest a way by which we can compress the data during spool process.
Should I first create a compressed temporary table from the oracle table then spool it?
CREATE TABLE XXX COMPRESS FOR QUERY AS SELECT * FROM ABC;
Your problem is probably because you are introducing a TON of trailing spaces by using set linesize 32000 and not trimming.
Add this to your script just before the spool command:
Depending on how many columns and the size of the data being extracted, this can significantly reduce the filesize.
Also, you might want to change to set colsep '|' (without the double quotes), just be sure the data you're extracting doesn't contain pipes as well (or use another delimiter)