Compressing data during spooling from oracle table

Question

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;

Answer 1

Let's say your script with definition of sqlplus is called script.sh. Isn't

script.sh | gzip > data.csv.gz

what you're looking for?

Answer 2

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:

set trimspool on

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)

Answer 3

I have modified my code as follows and it is working fine now:

mknod ../Temp/ABC.csv p
nohup gzip -c < ../Temp/ABC.csv > ../Files/ABC.gz &
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
set trimspool on
spool ./Temp/ABC.csv
Select COLUMN1,COLUMN2 from ABC;
spool off
EOF