Oracle export 2 gig files
Oracle Database Tips by Donald Burleson
Question: I'm on a 32-bit server and I cannot use the exp
command because the dmp files gets greater than 2 gigabytes.
How do I get around the 2 gig file size limit on UNIX? I'm
Answer: Very large Oracle tables (greater than 2 gig,
2**10) can be exported using the Oracle exp utility, with the use of
the UNIX split and compress commands. Here is a sample exp parfile
for a compressed export:
Notice that the output of this export is being directed to a file
called COMPRESS_PIPE. The first step to running a split, compressed
export is to create a named pipe for the compress and the split.
This will create a compressed, split pipe for an export.
rm ?f compress_pipe
rm ?f spilt_pipe
mknod compress_pipe p
mknod split_pipe p
chmod g+w compress_pipe
chmod g+w split_pipe
Now that the pipes are in place, we can submit our export, using the
exp parameter file. Note that the export job must be submitted from
the directory where the pipes exist, and in the directory that will
contain the exported dump files.
?b500m < split_pipe > /tmp/exp_tab &
nohup compress < compress_pipe > split_pipe &
nohup exp parfile=export_tab.par file=compress_pipe > exp_tab.list
These script commands will submit a split compressed export.
Note that the UNIX split command will place the output from the
export into files called XAA, XAB, XAC, each in 500-megabyte chunks.
Now that we have the file exported into manageable pieces, we can
import the file by reversing the process, piping the import utility
through the uncompress and cat UNIX commands. Below, the first
statement concatenates the file back together into the SPLIT_PIPE
The second command uncompresses the data from
SPLIT_PIPE and places the uncompressed, un-split file into
EXPORT_PIPE. The third statement runs the import utility using
EXPORT_PIPE as the input file.
nohup cat xaa xab xac xad >
nohup uncompress ?c split_pipe > export_pipe &
nohup imp file=export_pipe
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.