Database Reference
In-Depth Information
SQLLDR
SQLLDR is Oracle's high-speed, bulk data loader. It is an extremely useful tool for getting data into an Oracle database
from a variety of flat file formats. SQLLDR can be used to load enormous amounts of data in an amazingly short
period of time. It has two modes of operation:
•
Conventional path
: SQLLDR will employ SQL inserts on our behalf to load data.
•
Direct path
: SQLLDR does not use SQL in this mode; it formats database blocks directly.
The direct path load allows you to read data from a flat file and write it directly to formatted database blocks,
bypassing the entire SQL engine, undo generation and, optionally, redo generation at the same time. Parallel direct
path load is among the fastest ways to go from having no data to a fully loaded database.
We will not cover every single aspect of SQLLDR. For all of the details, refer to the
Oracle Database Utilities
manual, which dedicates seven chapters to SQLLDR in Oracle 12
c
Release 1. The fact that it is covered in seven
chapters is notable, since every other utility, such as DBVERIFY, DBNEWID, and LogMiner get one chapter or less. For
complete syntax and all of the options, I will again refer you to the
Oracle Database Utilities
manual, as this chapter is
intended to answer the “How do I . . .?” questions that a reference manual does not address.
It should be noted that the Oracle Call Interface (OCI) allows you to write your own direct path loader using C.
This is useful when the operation you want to perform is not feasible in SQLLDR or when seamless integration with
your application is desired. SQLLDR is a command-line tool (i.e., it's a separate program). It is not an API or anything
that can be “called from PL/SQL,” for example.
If you execute SQLLDR from the command line with no inputs, it gives you the following help:
$ sqlldr
SQL*Loader: Release 12.1.0.1.0 - Production on Sun Mar 9 11:57:29 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default
FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
