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:
CREATE TABLE ET_CUSTOMER_ADDRESS
(
"CA_ADDRESS_SK" NUMBER
,"CA_ADDRESS_ID" CHAR(16)
.
. .
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir:'gunzip' OPTIONS '-c'
BADFILE log_dir: 'CUSTOMER_ADDRESS.bad'
LOGFILE log_dir: 'CUSTOMER_ADDRESS.log'
FIELDS TERMINATED BY '|'
. . .
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
|
|
