2 GB File limit on writing file using Oracle SQLPlus in Unix

Requirement

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

[sourcecode language="bash"]

#creates named pipe in /tmp directory
mknod /tmp/aug11_pipe p

#sed used to remove extra tab space coming in sqlplus spool ouput
#compress command writes compressed file to disk
sed 's/[\t ]*//g' < /tmp/aug11_pipe | compress  > gl_aug11_pipe.csv.Z &

sqlplus -s apps/password <<EOF >/dev/null

set colsep ','
set feedback off
set linesize 900
set newpage 0
set pagesize 0
set termout off
set trimspool on
set verify off

SPOOL /tmp/aug11_pipe

#query output which is required
#could be any query that gives huge amount of data
SELECT gh.je_header_id, gh.last_update_date, gh.last_updated_by,
gh.set_of_books_id, gh.je_category, gh.je_source, '="'||gh.period_name||'"',
replace(gh.NAME,',',' ') name, gh.currency_code, gh.status, gh.date_created,
gh.default_effective_date, gh.creation_date, gh.created_by,
gh.je_batch_id, gh.posted_date, replace(gh.description,',',' ') description, gh.control_total,
gh.running_total_dr, gh.running_total_cr,
gh.running_total_accounted_dr, gh.running_total_accounted_cr,
gh.currency_conversion_rate, gh.currency_conversion_type,
gh.currency_conversion_date, gl.je_line_num, gl.accounted_dr,
gl.accounted_cr, '="'||gl.code_combination_id||'"', '="'||gc.segment2||'"' ACCOUNT,
'="'||gc.segment5||'"' sub_accountremove
FROM apps.gl_je_headers gh,
apps.gl_je_lines gl,
apps.gl_code_combinations gc
WHERE gl.code_combination_id = gc.code_combination_id
AND gl.je_header_id = gh.je_header_id
AND gh.period_name = 'AUG-11';

SPOOL OFF
EXIT
EOF

echo dump created

#delete the pipe explicitly
rm /tmp/aug11_pipe
[/sourcecode]