Search notes:

Oracle: SQL Loader

SQL*Loader is used to load data from external files (such as CSV files) into a table.
These external files are called data files. They must not be confused with database data files.
Prior to running SQL*Loader, the destination tables must be created as SQL*Loader never creates destination tables.

Options / keywords

The values of the options («keywords») are used by SQL*Loader to determine information such as
These values can either be indicated on the command line or in a control file.
Name Description Default
userid ORACLE username/password
control control file name
log log file name At least when being used in a control file, this option does not honor a different directory.
bad bad file name At least when being used in a control file, this option does not honor a different directory.
discard discard file name
discardmax number of discards to allow all
skip number of logical records to skip 0
load number of logical records to load all
errors number of errors to allow 50
rows number of rows in conventional path bind array or between direct path data saves With conventional path: 250; with direct path: all
bindsize size of conventional path bind array in bytes 1048576
silent suppress messages during run (header,feedback,errors,discards,partitions)
direct use direct path FALSE
parfile parameter file: name of file that contains parameter specifications
parallel do parallel load FALSE
file file to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions FALSE
skip_index_maintenance do not maintain indexes, mark affected indexes as unusable FALSE
commit_discontinued commit loaded rows when load is discontinued FALSE
readsize size of read buffer 1048576
external_table use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows number of rows for direct path column array 5000
streamsize size of direct path stream buffer in bytes 256000
multithreading use multithreading in direct path
resumable enable or disable resumable for current session FALSE
resumable_name text string to help identify resumable statement
resumable_timeout wait time (in seconds) for RESUMABLE 7200
date_cache size (in entries) of date conversion cache 1000
no_index_errors abort load on any index errors FALSE
partition_memory direct path partition memory limit to start spilling (kb) 0
table Table for express mode load
date_format Date format for express mode load
timestamp_format Timestamp format for express mode load
terminated_by terminated by character for express mode load
enclosed_by enclosed by character for express mode load
optionally_enclosed_by optionally enclosed by character for express mode load
characterset characterset for express mode load
degree_of_parallelism degree of parallelism for express mode load and external table load
trim trim type for express mode load and external table load
csv csv format data files for express mode load
nullif table level nullif clause for express mode load
field_names field names setting for first record of data files for express mode load
dnfs_enable option for enabling or disabling Direct NFS (dNFS) for input data files FALSE
dnfs_readbuffers the number of Direct NFS (dNFS) read buffers 4
sdf_prefix prefix to append to start of every LOB File and Secondary Data File
help display help messages FALSE
empty_lobs_are_null set empty LOBs to null FALSE
defaults direct path default value loading; EVALUATE_ONCE, EVALUATE_EVERY_ROW, IGNORE, IGNORE_UNSUPPORTED_EVALUATE_ONCE, IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW
direct_path_lock_wait wait for access to table when currently locked FALSE

SQL*Loader vs Data Pump

SQL*Loader is great at importing data into a database. However, it does not manage metadata very well and is not really usable to move data and/or table definitions from one database to another. These kind of tasks are much better handled by Data Pump.

SQL*Loader express mode

In SQL Loader express mode, no control file is needed.
Express mode is activated with the TABLE parameter: 
sqlldr user/password@db table=tab_xyz
SQL*Loader will then use the destination table (tab_xyz) structure to determine the input data's data types and columns.

Direct path loads

Direct path loads are specified with 
sqlldr DIRECT=TRUE …
The database must be prepared for direct path loads by running $ORACLE_HOME/rdbms/admin/catldr.sql.

See also

SQL Loader examples
control file
proc append in SAS to bulk load data from SAS to Oracle.
Installing Instant Client
The SQLcl and SQL Developer select hint /*loader*/ causes an SQL result set to be returned in a format that is suitable for SQL*Loader.
EXP and IMP
Compare SQL*Loader with external tables for importing data into a database.

Index