Loading large datasets with SQL*Loader

Loading large files has always been problematic with Oracle, especially in the days of 32-bit servers where you needed to split the files into 2 gig chunks and paste them back together to load the data.  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. 

As we see, using named pipes for large data files is a pain and Oracle 11g has addressed this issue in SQL*Loader.

This blog has an interesting note on a new preprocessor directive in SQL*Loader.  The new "preprocessor directive allows you to manage a large file without having to create a named pipe to unzip a large incoming file.

The blog author offers this code example of how to use the preprocessor directive to avoid running sqlldr through a named pipe with gzip:

---