Dump of huge data is required from oracle database. Say, dump of GL data (specific query output and not complete table dump) is required for particular month.
I simply spool the output of query and what I observe is - it always produces 2GB output file, for few months I tested, without giving any error message or warning.
So, I google for "2GB Unix oracle SQLPlus" and I find number of links mentioning this problem
unix 2GB oracle export
One of the solution involves using named pipe.
A named pipe doesn't write data to disk, but instead write to buffer in memory. Writer writes at one end and Reader reads from other end.
Named Pipe
- named pipe is actually file in file-system
- Like unnamed pipe it is used for Inter process Communication
- Unlike unnamed pipe it is system-persistent, that is, it exists beyond process life and has to be created and deleted explicitly.
- Process that reads or writes to pipe blocks until the other end of pipes performs read or write operation on same
Create a named pipe, redirect data written to it to sed command.
Removes extra tabs coming in spool file(spool usually gives extra tabs when colsep is used), and then pipe output to compress command which writes compressed data to disk.
Sqlplus will write data at one end to named pipe and following command will write compressed data to disk.
cat /tmp/aug11_pipe | compress > gl_aug11_pipe.csv
