Compress/zip Sqlplus Spool Output Automatically with Sql Execution Using UNIX Pipe

advertisements

_____________________________________________________________________________________________________________________

We generally use the oracle sqlplus spooling feature to log the output of the sql commands. If the output of the SQL command is too big in size, in such cases better to zip/compress the spool file. Again the available free space in the file system is too low to make the zip after creating the spool file is bit risky. Sometimes there will not be room for hold the actual and zip file during the compress process. In such situation you can use this method/script to compress the file in parallel while spooling by using the unix pipe.

Here is the scirpt

mknod /tmp/spool.pipe p

nohup gzip -c < /tmp/spool.pipe > /home/oracle/st/output.gz &

sqlplus / as sysdba << EOF

set escape on

spool /tmp/spool.pipe

select * from all_objects;

spool off

EOF

rm /tmp/spool.pipe

 

Output of the execution

68047 rows selected.

SQL> SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

[1]+  Done                    nohup gzip -c < /tmp/spool.pipe > /home/oracle/st/output.gz

-rw-r--r-- 1 oracle oinstall  1654558 Feb  6 07:50 output.gz ==ècompressed

 

$ gunzip output.gz

-rw-r--r-- 1 oracle oinstall 71721608 Feb  6 07:50 output==èUncomressed

_____________________________________________________________________________________________________________________

Website Stats