Oracle7 Server Utilities User's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Additional Information: The command to invoke SQL*Loader is operating system-dependent. The following examples use the UNIX-based name, "sqlldr". See your Oracle operating system-specific documentation for the correct command for your system.
If you invoke SQL*Loader with no keywords, SQL*Loader displays a help screen with the available keywords and default values. The following example shows default values that are the same on all operating systems.
sqlldr
...
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
(System-dependent default)
silent -- Suppress messages during run
(header, feedback, errors, discards)
direct -- Use direct path
(Default FALSE)
parfile -- Parameter file: name of file that contains
parameter specifications
parallel - Perform parallel load
(Default FALSE)
file - File to allocate extents from
For example, :
SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis,
DISCARDMAX=5
They can also be specified in a separate file specified by the keyword PARFILE (see page 6 - 5). These alternative methods are useful for keyword entries that seldom change. Keywords specified in this manner can still be overridden from the command line.
Keyword | Identifies | |
BAD | Bad File | |
A bad file filename specified on the command line becomes the bad file associated with the first INFILE statement in the control file. If the bad file filename was also specified in the control file, the command-line value overrides it.
BINDSIZE | Maximum sizes |
Keyword | Identifies | |
CONTROL | Control File | |
DATA | Datafile |
DIRECT | Data path |
DISCARD | Discard file |
A discard file filename specified on the command line becomes the discard file associated with the first INFILE statement in the control file. If the discard file filename was also specified in the control file, the command-line value overrides it.
DISCARDMAX | Discards to allow |
ERRORS | Errors to allow |
Keyword | Identifies | |
FILE | File to Load Into | |
LOAD | Records to load |
LOG | Log File |
PARFILE | Parameter File |
SQLLDR PARFILE=example.par
and the parameter file could have the following contents:
userid=scott/tiger
control=example.ctl
errors=9999
log=example.log
Note: Although it is not usually important, on some systems it may be necessary to have no spaces around the equal sign ("=") in the parameter specifications.
PARALLEL | Parallel Load |
Keyword | Identifies | |
ROWS | Rows per commit | |
Direct path, loads only: ROWS identifies the number of rows you want to read from the data file before a data save. The default is to save data once at the end of the load. For more information, see "Data Saves" .
Because the direct load is optimized for performance, it uses buffers that are the same size and format as the system's I/O blocks. Only full buffers are written to the database, so the value of ROWS is approximate.
SILENT | Feedback mode |
SQL*Loader: Production on Wed Feb 24 15:07:23...
Copyright (c) Oracle Corporation...
As SQL*Loader executes, you also see feedback messages on the screen, like this:
Commit point reached - logical record count 20
SQL*Loader may also display data error messages like these:
Record 4: Rejected - Error on table EMP
ORA-00001: unique constraint <name> violated
You can suppress these messages by specifying SILENT with an argument. For example, you can suppress the header and feedback messages that normally appear on the screen with the following command-line argument:
SILENT=(HEADER, FEEDBACK)
Use the appropriate keyword to suppress:
HEADER | Suppresses the SQL*Loader header messages that normally appear on the screen. Header messages still appear in the log file. |
FEEDBACK | Suppresses the "commit point reached" feedback messages that normally appear on the screen. |
ERRORS | Suppresses the data error messages in the log file that occur when a record generates an Oracle error that causes it to be written to the bad file. A count of rejected records still appears. |
DISCARDS | Suppresses the messages in the log file for each record written to the discard file. |
ALL | All of the above. |
Keyword | Identifies | |
SKIP | Records to skip | |
This parameter continues loads that have been interrupted for some reason. It is used for all conventional loads, for single-table direct loads, and for multiple-table direct loads when the same number of records were loaded into each table. It is not used for multiple table direct loads when a different number of records were loaded into each table. See "Continuing Multiple Table Direct Loads" for more information.
USERID | Userid |
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |