Oracle SQL*Loader Options
SQL*Loader provides
the following options, which can be specified either on the
command line or within a parameter file:
·
bad – A file that is created when
at least one record from the input file is rejected. The
rejected data records are placed in this file. A record could
be rejected for many reasons, including a non-unique key or a
required column being null.
·
bindsize
– [256000] The size of the bind array in bytes.
·
columnarrayrows – [5000] Specifies
the number of rows to allocate for direct path column arrays.
·
control – The name of the control
file. This file specifies the format of the data to be
loaded.
·
data – The name of the file that
contains the data to load.
·
direct – [FALSE] Specifies whether
or not to use a direct path load or conventional load.
·
discard – The name of the file
that contains the discarded rows. Discarded rows are those
that fail the WHEN clause condition when selectively loading
records.
·
discardmax – [ALL] The maximum
number of discards to allow.
·
errors – [50] The number of errors
to allow on the load.
·
external_table – [NOT_USED]
Determines whether or not any data will be loaded using
external tables. The other valid options include
GENERATE_ONLY and EXECUTE.
·
file – Used only with parallel
loads, this parameter specifies the file to allocate extents
from.
·
load – [ALL] The number of logical
records to load.
·
log – The name of the file used by
SQL*Loader to log results.
·
multithreading – The default is
TRUE on multiple CPU systems and FALSE on single CPU systems.
·
parfile – [Y] The name of the file
that contains the parameter options for SQL*Loader.
·
parallel – [FALSE] Specifies a
filename that contains index creation statements.
·
readsize – The size of the buffer
used by SQL*Loader when reading data from the input file.
This value should match that of bindsize.
·
resumable – [N] Enables and
disables resumable space allocation. When “Y”, the parameters
resumable_name and
resumable_timeout are utilized.
·
resumable_name – User defined string that helps identify a
resumable statement that has been suspended. This parameter
is ignored unless resumable = Y.
·
resumable_timeout – [7200 seconds] The time period in which an error
must be fixed. This parameter is ignored unless resumable
= Y.
·
rows – [64] The number of rows to
load before a commit is issued (conventional path
only). For direct path loads, rows are the number of rows to
read from the data file before saving the data in the
datafiles.
·
silent – Suppress errors during
data load. A value of ALL will suppress all load messages.
Other options include DISCARDS, ERRORS, FEERemote DBACK, HEADER, and
PARTITIONS.
·
skip – [0] Allows the skipping of
the specified number of logical records.
·
skip_unusable_indexes – [FALSE]
Determines whether SQL*Loader skips the building of indexes
that are in an unusable state.
·
skip_index_maintenance – [FALSE]
Stops index maintenance for direct path loads only.
·
streamsize – [256000] Specifies
the size of direct path streams in bytes.
·
userid – The Oracle username and
password.
To check which options are available in
any release of SQL*Loader use this command:
sqlldr help=y
The above is an excerpt from Oracle Utilities - Using
Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof
and More by Rampant TechPress (Dave Moore).
It’s only $19.95 and you can order the
book and get instant access to the online Oracle utilities scripts:
http://www.rampant-books.com/book_2003_1_utils.htm
|
