Donald K. Burleson Oracle Tips

Compressing and splitting a huge export file

With most tables growing into many gigabytes, few Oracle professionals know how to use Oracle export utility for large SAP tables. One of the real drawbacks of the Oracle export utility is the set of restrictions imposed by the UNIX operating system. As you probably know, some UNIX systems limit an individual file to two gigabytes. Fortunately, with a few tricks, very large tables can also be exported using the Oracle utility, and with the use of the UNIX split and compress commands, we can easily export a ten-gigabyte  table. The Listing below shows an example of a parameter file for an Oracle/SAP export parameter file.

file=compress_pipe

direct=y

rows=y

indexes=y

tables=(vbep)

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 (see Listing below). 

rm –f compress_pipe

rm –f split_pipe

mknod compress_pipe p

mknod split_pipe p

chmod g+w compress_pipe

chmod g+w split_pipe

Creating a compressed, split pipe for an export.

Now that the pipes are in place, we can submit our export, using the parameter file from Listing 5-3.  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 &

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 (see Listing below)

nohup cat xaa xab xac xad > split_pipe &

nohup uncompress –c split_pipe > export_pipe &

nohup imp file=export_pipe

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm