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 running Solaris.

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:

file=compress_pipe
direct=y
rows=y
indexes=y
tables=(2_gig_tablename)
buffer=1024000
log=exp_vbep.lst

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.

Nohup split ?b500m < split_pipe > /tmp/exp_tab &

nohup compress < compress_pipe > split_pipe &

nohup exp parfile=export_tab.par file=compress_pipe > exp_tab.list 2>&1 &

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 file.

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 > split_pipe &

nohup uncompress ?c split_pipe > export_pipe &

nohup imp file=export_pipe