Oracle
Tips & Tricks to
SKYROCKET Your Career!
If
you're not already a subscriber to Oracle Secrets Revealed,
you're missing out on a myriad of tips and techniques to help
you work better, faster and
smarter. Subscribe now and ignite your
career.
How It Works
SQL Loader processes the input data files according to the directions
in a text file called the control file which specifies the names and
locations of the source data files, the format of the data to be loaded
and the data transformations to be performed when loading.
As
the input files are processed, any records that do not pass format
checks are written to the bad file and any records that do not meet the
specified selection criteria are written to the discard file.
Records
that pass both format and selection criteria are written to the
specified target tables but they may still be rejected because of, for
example, constraint violations in which case they are written to the
bad file along with those records rejected for being invalid.
A
log file containing a detailed summary of the load, including a
description of any errors that occurred during the load is also
produced.
How To Use Oracle SQL Loader
Oracle SQL Loader is initiated from the command line and the various
parameters such as the name of the control file and the userid can be
specified at the same time but it is generally a lot easier to put all
these parameters into a parameter file, thereby saving on typing and
frustration when typos are made. Which of the conventional/direct
path/external path load types to use is a trade off between performance
and flexibility.
The
default load type is the conventional
load which creates and executes
SQL insert statements to load the data into the target tables. This
method is better when:
- other users need to be able update data in the
target tables whilst new data is being loaded into them;
- loading data into clustered tables;
- loading a relatively small amount of rows into
a large indexed table as
the load process makes a copy of the original index before merging in
the new keys (this is a relatively slow process for a large table);
- loading data into a large table with
referential or column check
integrity constraints as these constraints are disabled during a direct
path load and re-enabled when the load finishes requiring the whole
table to be checked;
- you want to ensure that a record will
be rejected if it causes an Oracle error, is formatted incorrectly or
violates a constraint on the target table;
- or insert triggers must be fired.
The
direct-path
load is initiated by specifying DIRECT=TRUE
when starting
SQL*Loader. This method writes formatted data blocks directly to the
target tables bypassing the SQL layer making loading faster but has the
restrictions mentioned above. This method should be used when you need
to load a large amount of data and need to maximise performance.
When
using external-path load
the source file has to be in a location
accessible to the database and specified by an Oracle directory and the
user must have been granted read and write access to the Oracle
directory.
Full details on sql loader can be found in the Oracle
utilities guide
Summary
Oracle SQL Loader is a very versatile tool with many different
parameters and options which can make it difficult to use. Probably the
most important thing to do when using it is to make sure the format of
the control file is correct (refer to the
Oracle
utilities guide
for the exact format), start with a small sample of data to ensure it
works and always check the bad and discard files to ensure that the
data you think should be loaded is actually being loaded.
Slash Costs, Sky-Rocket
Productivity and Maximise Uptime with Expert Oracle Consulting from
Smartsoft
