You use command-line parameters to start SQL*Loader, as described in the following topics:
Note:
Regular SQL*Loader and SQL*Loader express mode share some of the same parameters, but the behavior may be different. The parameter descriptions in this chapter are for regular SQL*Loader. The parameters for SQL*Loader express mode are described in SQL*Loader Express .
This section describes how to start SQL*Loader and specify parameters. It contains the following sections:
To display a help screen that lists all SQL*Loader parameters, along with a brief description and the default value of each one, enter sqlldr at the prompt and press Enter.
When you start SQL*Loader, you specify parameters to establish various characteristics of the load operation. You can separate the parameters by commas, but it is not necessary.
> sqlldr CONTROL=ulcase1.ctl LOG=ulcase1.log
Username: scott
Password: password
Specifying by position means that you enter a value, but not the parameter name. In the following example, the username scott is provided and then the name of the control file, ulcase1.ctl. You are prompted for the password:
> sqlldr scott ulcase1.ctl
Password: password
Once a parameter name is used, parameter names must be supplied for all subsequent specifications. No further positional specification is allowed. For example, in the following command, the CONTROL parameter is used to specify the control file name, but then the log file name is supplied without the LOG parameter. This would result in an error even though the position of ulcase1.log is correct:
> sqlldr scott CONTROL=ulcase1.ctl ulcase1.log
Instead, you would need to enter the following:
> sqlldr scott CONTROL=ulcase1.ctl LOG=ulcase1.log
See Also:
"Command-Line Parameters for SQL*Loader" for descriptions of all the command-line parameters
If the length of the command line exceeds the maximum line size for your system, then you can put certain command-line parameters in the control file by using the OPTIONS clause.
You can also group parameters together in a parameter file. You specify the name of this file on the command line using the PARFILE parameter when you start SQL*Loader.
These alternative ways of specifying parameters are useful when you often use the same parameters with the same values.
Parameter values specified on the command line override parameter values specified in either a parameter file or in the OPTIONS clause.
See Also:
To use SQL*Loader to load data across a network connection, you can specify a connect identifier in the connect string when you start the SQL*Loader utility. This identifier can specify a database instance that is different from the current instance identified by the setting of the ORACLE_SID environment variable for the current user. The connect identifier can be an Oracle Net connect descriptor or a net service name (usually defined in the tnsnames.ora file) that maps to a connect descriptor. Use of a connect identifier requires that you have Oracle Net Listener running (to start the default listener, enter lsnrctl start). The following example starts SQL*Loader for user scott using the connect identifier inst1:
> sqlldr CONTROL=ulcase1.ctl Username: scott@inst1 Password: password
The local SQL*Loader client connects to the database instance defined by the connect identifier inst1 (a net service name), and loads the data, as specified in the ulcase1.ctl control file.
Note:
To load data into a pluggable database (PDB), simply specify its connect identifier on the connect string when you start SQL*Loader.
See Also:
Oracle Database Net Services Administrator's Guide for more information about connect identifiers and Oracle Net Listener
Oracle Database Concepts for more information about PDBs
This section describes each SQL*Loader command-line parameter. The defaults and maximum values listed for these parameters are for UNIX-based systems. They may be different on your operating system. Refer to your Oracle operating system-specific documentation for more information.
Default: The name of the data file, with an extension of .bad
Purpose
Specifies the name or location, or both, of the bad file associated with the first data file specification.
Syntax and Description
BAD=[directory/][filename]
The bad file stores records that cause errors during insert or that are improperly formatted. If you specify the BAD parameter, you must supply either a directory or file name, or both. If there are rejected records, and you have not specified a name for the bad file, then the name defaults to the name of the data file with an extension or file type of .bad.
The directory parameter specifies a directory to which the bad file is written. The specification can include the name of a device or network node. The value of directory is determined as follows:
If the BAD parameter is not specified at all and a bad file is needed, then the default directory is the one in which the SQL*Loader control file resides.
If the BAD parameter is specified with a file name but no directory, then the directory defaults to the current directory.
If the BAD parameter is specified with a directory but no file name, then the specified directory is used and the default is used for the bad file name and extension.
The filename parameter specifies a file name recognized as valid on your platform. You must specify only a name (and extension, if one other than .bad is desired). Any spaces or punctuation marks in the file name must be enclosed in single quotation marks.
A bad file specified on the command line becomes the bad file associated with the first INFILE statement (if there is one) in the control file. The name of the bad file can also be specified in the SQL*Loader control file, using the BADFILE clause. If the bad file is specified in the control file, as well as on the command line, then the command-line value is used. If a bad file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system
See Also:
"Specifying the Bad File" for information about the format of bad files
Example
The following specification creates a bad file named emp1.bad in the current directory:
BAD=emp1
Default: 256000
Purpose
Syntax and Description
BINDSIZE=n
A bind array is an area in memory where SQL*Loader stores data that is to be loaded. When the bind array is full, the data is transmitted to the database. The bind array size is controlled by the BINDSIZE and READSIZE parameters.
The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS.
See Also:
Restrictions
The BINDSIZE parameter is used only for conventional path loads.
Example
The following BINDSIZE specification limits the maximum size of the bind array to 356,000 bytes.
BINDSIZE=356000
Default: 5000
Purpose
The COLUMNARRAYROWS parameter specifies the number of rows to allocate for direct path column arrays.
Syntax and Description
COLUMNARRARYROWS=n
The value for this parameter is not calculated by SQL*Loader. You must either specify it or accept the default.
Example
The following example specifies that 1000 rows are to be allocated for direct path column arrays.
COLUMNARRAYROWS=1000
Default: There is no default.
Purpose
The CONTROL parameter specifies the name of the SQL*Loader control file that describes how to load the data.
Syntax and Description
CONTROL=control_file_name
If a file extension or file type is not specified, then it defaults to .ctl. If the CONTROL parameter is not specified, then SQL*Loader prompts you for it.
If the name of your SQL*Loader control file contains special characters, then your operating system may require that they be preceded by an escape character. Also, if your operating system uses backslashes in its file system paths, then you may need to use multiple escape characters or to enclose the path in quotation marks. See your Oracle operating system-specific documentation for more information.
See Also:
SQL*Loader Control File Reference for a detailed description of the SQL*Loader control file
Example
The following example specifies a control file named emp1. It is automatically given the default extension of .ctl.
CONTROL=emp1
Default: The same name as the control file, but with an extension of .dat.
Purpose
The DATA parameter specifies the name(s) of the data file(s) containing the data to be loaded.
Syntax and Description
DATA=data_file_name
If you do not specify a file extension, then the default is .dat.
The file specification can contain wildcards (only in the file name and file extension, not in a device or directory name). An asterisk (*) represents multiple characters and a question mark (?) represents a single character. For example:
DATA='emp*.dat' DATA='m?emp.dat'
To list multiple data file specifications (each of which can contain wild cards), the file names must be separated by commas.
If the file name contains any special characters (for example, spaces, *, ?, ), then the entire name must be enclosed within single quotation marks.
The following are three examples of possible valid uses of the DATA parameter (the single quotation marks would only be necessary if the file name contained special characters):
DATA='file1','file2','file3','file4','file5','file6'
DATA='file1','file2' DATA='file3,'file4','file5' DATA='file6'
DATA='file1' DATA='file2' DATA='file3' DATA='file4' DATA='file5' DATA='file6'
Caution:
If multiple data files are being loaded and you are also specifying the BAD parameter, it is recommended that you specify only a directory for the bad file, not a file name. If you specify a file name, and a file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system.
If you specify data files on the command line with the DATA parameter and also specify data files in the control file with the INFILE clause, then the first INFILE specification in the control file is ignored. All other data files specified on the command line and in the control file are processed.
If you specify a file processing option along with the DATA parameter when loading data from the control file, then a warning message is issued.
Example
The following example specifies that a data file named employees.dat is to be loaded. The .dat extension is assumed as the default because no extension is provided.
DATA=employees
Default: Enabled (for 1000 elements). To completely disable the date cache feature, set it to 0 (zero).
Purpose
The DATE_CACHE parameter specifies the date cache size (in entries). The date cache is used to store the results of conversions from text strings to internal date format. The cache is useful because the cost of looking up dates is much less than converting from text format to date format. If the same dates occur repeatedly in the data file, then using the date cache can improve the speed of a direct path load.
Syntax and Description
DATE_CACHE=n
Every table has its own date cache, if one is needed. A date cache is created only if at least one date or timestamp value is loaded that requires data type conversion in order to be stored in the table.
The date cache feature is enabled by default. The default date cache size is 1000 elements. If the default size is used and the number of unique input values loaded exceeds 1000, then the date cache feature is automatically disabled for that table. However, if you override the default and specify a nonzero date cache size and that size is exceeded, then the cache is not disabled.
You can use the date cache statistics (entries, hits, and misses) contained in the log file to tune the size of the cache for future similar loads.
See Also:
Restrictions
The date cache feature is only available for direct path and external tables loads.
Example
The following specification completely disables the date cache feature.
DATE_CACHE=0
Use the SQL*Loader DEFAULTS command to control evaluation and loading of default expressions.
Purpose
Default: EVALUATE_ONCE, unless a sequence is involved, then the default is EVALUATE_EVERY_ROW.
The DEFAULTS parameter is only applicable to direct path loads.
Syntax and Description
DEFAULTS={IGNORE | IGNORE_UNSUPPORTED_EVALUATE_ONCE | IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW |
EVALUATE_ONCE | EVALUATE_EVERY_ROW}
The behavior of each of the options is as follows:
IGNORE — Default clauses on columns are ignored.
IGNORE_UNSUPPORTED_EVALUATE_ONCE —Evaluate default expressions once at the start of the load. Unsupported default expressions are ignored. If the DEFAULTS parameter is not used, then default expressions are evaluated once, unless the default expression references a sequence, in which case every row is evaluated.
IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW — Evaluate default expressions in every row, ignoring unsupported default clauses.
EVALUATE_ONCE —Evaluate default expressions once at the start of the load. If the DEFAULTS parameter is not used, then default expressions are evaluated once, unless the default references a sequence, in which case every row is evaluated. An error is issued for unsupported default expression clauses. (This is the default option for this parameter.)
EVALUATE_EVERY_ROW — Evaluate default expressions in every row and issue an error for unsupported defaults.
Example Usage
Suppose you create a table named test and a SQL*Loader control file named test.ctl, as follows:
create table test ( c0 varchar2(10), c1 number default '100' ) ; test.ctl: load data infile * truncate into table test fields terminated by ',' trailing nullcols ( c0 char ) begindata 1,
To then load a NULL into c1, issue the following statement:
sqlldr scott/tiger t.ctl direct=true defaults=ignore
To load the default value of 100 into c1, issue the following statement:
sqlldr scott/tiger t.ctl direct=true
Default: NONE
Purpose
The DEGREE_OF_PARALLELISM parameter specifies the degree of parallelism to use during the load operation.
Syntax and Description
DEGREE_OF_PARALLELISM=[degree-num|DEFAULT|AUTO|NONE]
If a degree-num is specified, then it must be a whole number value from 1 to n.
If DEFAULT is specified, then the default parallelism of the database (not the default parameter value of AUTO) is used.
If AUTO is used, then the Oracle database automatically sets the degree of parallelism for the load.
If NONE is specified, then the load is not performed in parallel.
See Also:
Oracle Database VLDB and Partitioning Guide for more information about parallel execution
Restrictions
The DEGREE_OF_PARALLELISM parameter is valid only when the external table load method is used.
Example
The following example sets the degree of parallelism for the load to 3.
DEGREE_OF_PARALLELISM=3
Default: FALSE
Purpose
The DIRECT parameter specifies the load method to use, either conventional path or direct path.
Syntax and Description
DIRECT=[TRUE | FALSE]
A value of TRUE specifies a direct path load. A value of FALSE specifies a conventional path load.
See Also:
Example
The following example specifies that the load be performed using conventional path mode.
DIRECT=FALSE
Use the SQL*Loader command DIRECT_PATH_LOCK_WAIT to control direct path load behavior when waiting for table locks.
Purpose
Default: FALSE
Direct path loads must lock the table before the load can proceed. The DIRECT_PATH_LOCK_WAIT command controls the direct path API’s behavior while waiting for a lock.
Syntax and Description
DIRECT_PATH_LOCK_WAIT = {TRUE | FALSE}
TRUE — Direct path waits until it can get a lock on the table before proceeding with the load.
FALSE — This is the default. The direct path API tries to lock the table multiple times and waits one second between attempts. The maximum number of attempts made is 30. If the table cannot be locked after 30 attempts, then the direct path API returns the error that was generated when trying to lock the table.
Default: The same file name as the data file, but with an extension of .dsc.
Purpose
The DISCARD parameter lets you optionally specify a discard file to store records that are neither inserted into a table nor rejected. They are not bad records, they simply did not match any record-selection criteria specified in the control file, such as a WHEN clause for example.
Syntax and Description
DISCARD=[directory/][filename]
If you specify the DISCARD parameter, then you must supply either a directory or file name, or both.
The directory parameter specifies a directory to which the discard file will be written. The specification can include the name of a device or network node. The value of directory is determined as follows:
If the DISCARD parameter is not specified at all, but the DISCARDMAX parameter is, then the default directory is the one in which the SQL*Loader control file resides.
If the DISCARD parameter is specified with a file name but no directory, then the directory defaults to the current directory.
If the DISCARD parameter is specified with a directory but no file name, then the specified directory is used and the default is used for the name and the extension.
The filename parameter specifies a file name recognized as valid on your platform. You must specify only a name (and extension, if one other than .dsc is desired). Any spaces or punctuation marks in the file name must be enclosed in single quotation marks.
If neither the DISCARD parameter nor the DISCARDMAX parameter is specified, then a discard file is not created even if there are discarded records.
If the DISCARD parameter is not specified, but the DISCARDMAX parameter is, and there are discarded records, then the discard file is created using the default name and the file is written to the same directory in which the SQL*Loader control file resides.
Caution:
If multiple data files are being loaded and you are also specifying the DISCARD parameter, it is recommended that you specify only a directory for the discard file, not a file name. If you specify a file name, and a file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system.
A discard file specified on the command line becomes the discard file associated with the first INFILE statement (if there is one) in the control file. If the discard file is also specified in the control file, then the command-line value overrides it. If a discard file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system.
See Also:
"Discarded and Rejected Records" for information about the format of discard files
Example
Assume that you are loading a data file named employees.dat. The following example supplies only a directory name so the name of the discard file will be employees.dsc and it will be created in the mydir directory.
DISCARD=mydir/
Default: ALL
Purpose
The DISCARDMAX parameter specifies the number of discard records to allow before data loading is terminated.
Syntax and Description
DISCARDMAX=n
To stop on the first discarded record, specify a value of 0.
If DISCARDMAX is specified, but the DISCARD parameter is not, then the name of the discard file is the name of the data file with an extension of .dsc.
Example
The following example allows 25 records to be discarded during the load before it is terminated.
DISCARDMAX=25
Default: TRUE
Purpose
The DNFS_ENABLE parameter lets you enable and disable use of the Direct NFS Client on input data files during a SQL*Loader operation.
Syntax and Description
DNFS_ENABLE=[TRUE|FALSE]
The Direct NFS Client is an API that can be implemented by file servers to allow improved performance when an Oracle database accesses files on those servers.
SQL*Loader uses the Direct NFS Client interfaces by default when it reads data files over 1 GB. For smaller files, the operating system's I/O interfaces are used. To use the Direct NFS Client on all input data files, use DNFS_ENABLE=TRUE.
To disable use of the Direct NFS Client for all data files, specify DNFS_ENABLE=FALSE.
The DNFS_READBUFFERS parameter can be used to specify the number of read buffers used by the Direct NFS Client; the default is 4.
See Also:
Oracle Grid Infrastructure Installation Guide for your platform for more information about enabling the Direct NFS Client
Example
The following example disables use of the Direct NFS Client on input data files during the load.
DNFS_ENABLE=FALSE
Default: 4
Purpose
The DNFS_READBUFFERS parameter lets you control the number of read buffers used by the Direct NFS Client. The Direct NFS Client is an API that can be implemented by file servers to allow improved performance when an Oracle database accesses files on those servers.
Syntax and Description
DNFS_READBUFFERS=n
Using larger values might compensate for inconsistent I/O from the Direct NFS Client file server, but it may result in increased memory usage.
See Also:
Oracle Grid Infrastructure Installation Guide for your platform for more information about enabling the Direct NFS Client
Restrictions
To use this parameter without also specifying the DNFS_ENABLE parameter, the input file must be larger than 1 GB.
Example
The following example specifies 10 read buffers for use by the Direct NFS Client.
DNFS_READBUFFERS=10
If the SQL*Loader EMPTY_LOBS_ARE_NULL parameter is specified, then any LOB column for which there is no data available is set to NULL rather than to an empty LOB.
Default: FALSE
Purpose
Setting LOB columns for which there is no data available to NULL negates the need to do this through post-processing after the data is loaded.
Syntax and Description
EMPTY_LOBS_ARE_NULL = {TRUE | FALSE}
The EMPTY_LOBS_ARE_NULL parameter can be specified on the SQL*Loader command line and also on the OPTIONS clause in a SQL*Loader control file.
Restrictions
None.
Example
In the following example, c1 will be NULL instead of an empty lob.
create table t ( c0 varchar2(10), c1 clob ) ; sqlldr control file: options (empty_lobs_are_null=true) load data infile * truncate into table t fields terminated by ',' trailing nullcols ( c0 char, c1 char ) begindata 1,,
Default: 50
Purpose
The ERRORS parameter specifies the maximum number of insert errors to allow.
Syntax and Description
ERRORS=n
If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. Any data inserted up to that point is committed.
To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.
SQL*Loader maintains the consistency of records across all tables. Therefore, multitable loads do not terminate immediately if errors exceed the error limit. When SQL*Loader encounters the maximum number of errors for a multitable load, it continues to load rows to ensure that valid rows previously loaded into tables are loaded into all tables and rejected rows are filtered out of all tables.
In all cases, SQL*Loader writes erroneous records to the bad file.
Example
The following example specifies a maximum of 25 insert errors for the load. After that, the load is terminated.
ERRORS=25
Default: NOT_USED
Purpose
The EXTERNAL_TABLE parameter instructs SQL*Loader whether to load data using the external tables option.
Syntax and Description
EXTERNAL_TABLE=[NOT_USED | GENERATE_ONLY | EXECUTE]
The possible values are as follows:
NOT_USED - the default value. It means the load is performed using either conventional or direct path mode.
GENERATE_ONLY - places all the SQL statements needed to do the load using external tables, as described in the control file, in the SQL*Loader log file. These SQL statements can be edited and customized. The actual load can be done later without the use of SQL*Loader by executing these statements in SQL*Plus.
EXECUTE - attempts to execute the SQL statements that are needed to do the load using external tables. However, if any of the SQL statements returns an error, then the attempt to load stops. Statements are placed in the log file as they are executed. This means that if a SQL statement returns an error, then the remaining SQL statements required for the load will not be placed in the log file.
If you use EXTERNAL_TABLE=EXECUTE and also use the SEQUENCE parameter in your SQL*Loader control file, then SQL*Loader creates a database sequence, loads the table using that sequence, and then deletes the sequence. The results of doing the load this way will be different than if the load were done with conventional or direct path. (For more information about creating sequences, see CREATE SEQUENCE in Oracle Database SQL Language Reference.)
Note:
When the EXTERNAL_TABLE parameter is specified, any datetime data types (for example, TIMESTAMP) in a SQL*Loader control file are automatically converted to a CHAR data type and use the external tables date_format_spec clause. See "date_format_spec".
Note that the external table option uses directory objects in the database to indicate where all input data files are stored and to indicate where output files, such as bad files and discard files, are created. You must have READ access to the directory objects containing the data files, and you must have WRITE access to the directory objects where the output files are created. If there are no existing directory objects for the location of a data file or output file, then SQL*Loader will generate the SQL statement to create one. Therefore, when the EXECUTE option is specified, you must have the CREATE ANY DIRECTORY privilege. If you want the directory object to be deleted at the end of the load, then you must also have the DROP ANY DIRECTORY privilege.
Note:
The EXTERNAL_TABLE=EXECUTE qualifier tells SQL*Loader to create an external table that can be used to load data and then executes the INSERT statement to load the data. All files in the external table must be identified as being in a directory object. SQL*Loader attempts to use directory objects that already exist and that you have privileges to access. However, if SQL*Loader does not find the matching directory object, then it attempts to create a temporary directory object. If you do not have privileges to create new directory objects, then the operation fails.
To work around this, use EXTERNAL_TABLE=GENERATE_ONLY to create the SQL statements that SQL*Loader would try to execute. Extract those SQL statements and change references to directory objects to be the directory object that you have privileges to access. Then, execute those SQL statements.
When using a multi-table load, SQL*Loader does the following:
Creates a table in the database that describes all fields in the input data file that will be loaded into any table.
Creates an INSERT statement to load this table from an external table description of the data.
Executes one INSERT statement for every table in the control file.
To see an example of this, run case study 5, but add the EXTERNAL_TABLE=GENERATE_ONLY parameter. To guarantee unique names in the external table, SQL*Loader uses generated names for all fields. This is because the field names may not be unique across the different tables in the control file.
See Also:
"SQL*Loader Case Studies" for information on how to access case studies
Restrictions
Julian dates cannot be used when you insert data into a database table from an external table through SQL*Loader. To work around this, use TO_DATE and TO_CHAR to convert the Julian date format, as shown in the following example:
TO_CHAR(TO_DATE(:COL1, 'MM-DD-YYYY'), 'J')
Built-in functions and SQL strings cannot be used for object elements when you insert data into a database table from an external table.
Example
EXTERNAL_TABLE=EXECUTE
Default: There is no default.
Purpose
The FILE parameter specifies the database file from which to allocate extents.
See Also:
Syntax and Description
FILE=tablespace_file
By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention.
Restrictions
The FILE parameter is used only for direct path parallel loads.
The SQL*Loader HELP command displays online help for the SQL*Loader utility.
Default: FALSE
Syntax and Description
HELP = [TRUE | FALSE]
If HELP=TRUE is specified, then SQL*Loader displays a summary of all SQL*Loader command-line parameters.
You can also display a summary of all SQL*Loader command-line parameters by entering sqlldr -help on the command line.
Default: All records are loaded.
Purpose
The LOAD parameter specifies the maximum number of records to load.
Syntax and Description
LOAD=n
To test that all parameters you have specified for the load are set correctly, use the LOAD parameter to specify a limited number of records rather than loading all records. No error occurs if fewer than the maximum number of records are found.
Example
The following example specifies that a maximum of 10 records be loaded.
LOAD=10
For external tables method loads, only successfully loaded records are counted toward the total. So if there are 15 records in the input data file and records 2 and 4 are bad, then the following records are loaded into the table, for a total of 10 records - 1, 3, 5, 6, 7, 8, 9, 10, 11, and 12.
For conventional and direct path loads, both successful and unsuccessful load attempts are counted toward the total. So if there are 15 records in the input data file and records 2 and 4 are bad, then only the following 8 records are actually loaded into the table - 1, 3, 5, 6, 7, 8, 9, and 10.
Default: The name of the control file, with an extension of .log.
Purpose
The LOG parameter specifies a directory path, or file name, or both for the log file that SQL*Loader uses to store logging information about the loading process.
Syntax and Description
LOG=[[directory/][log_file_name]]
If you specify the LOG parameter, then you must supply a directory name, or a file name, or both.
If no directory name is specified, it defaults to the current directory.
If a directory name is specified without a file name, then the default log file name is used.
Example
The following example creates a log file named emp1.log in the current directory. The extension .log is used even though it is not specified, because it is the default.
LOG=emp1
Default: TRUE on multiple-CPU systems, FALSE on single-CPU systems
Purpose
Allows stream building on the client system to be done in parallel with stream loading on the server system.
Syntax and Description
MULTITHREADING=[TRUE | FALSE]
By default, the multithreading option is always enabled (set to TRUE) on multiple-CPU systems. In this case, the definition of a multiple-CPU system is a single system that has more than one CPU.
On single-CPU systems, multithreading is set to FALSE by default. To use multithreading between two single-CPU systems, you must enable multithreading; it will not be on by default.
Restrictions
The MULTITHREADING parameter is available only for direct path loads.
Multithreading functionality is operating system-dependent. Not all operating systems support multithreading.
Example
The following example enables multithreading on a single-CPU system. On a multiple-CPU system it is enabled by default.
MULTITHREADING=TRUE
Default: FALSE
Purpose
The NO_INDEX_ERRORS parameter determines whether indexing errors are tolerated during a direct path load.
Syntax and Description
NO_INDEX_ERRORS=[TRUE | FALSE]
A setting of NO_INDEX_ERRORS=FALSE means that if a direct path load results in an index becoming unusable then the rows are loaded and the index is left in an unusable state. This is the default behavior.
A setting of NO_INDEX_ERRORS=TRUE means that if a direct path load results in any indexing errors, then the load is aborted. No rows are loaded and the indexes are left as they were.
Restrictions
The NO_INDEX_ERRORS parameter is valid only for direct path loads. If it is specified for conventional path loads, then it is ignored.
Example
NO_INDEX_ERRORS=TRUE
Default: FALSE
Purpose
The PARALLEL parameter specifies whether loads that use direct path or external tables can operate in multiple concurrent sessions to load data into the same table.
Syntax and Description
PARALLEL=[TRUE | FALSE]
See Also:
Restrictions
The PARALLEL parameter is not valid in conventional path loads.
Example
The following example specifies that the load will be performed in parallel.
PARALLEL=TRUE
Default: There is no default.
Purpose
The PARFILE parameter specifies the name of a file that contains commonly used command-line parameters.
Syntax and Description
PARFILE=file_name
Instead of specifying each parameter on the command line, you can simply specify the name of the parameter file. For example, a parameter file named daily_report.par might have the following contents:
USERID=scott CONTROL=daily_report.ctl ERRORS=9999 LOG=daily_report.log
For security reasons, you should not include your USERID password in a parameter file. SQL*Loader will prompt you for the password after you specify the parameter file at the command line, for example:
sqlldr PARFILE=daily_report.par
Password: password
Restrictions
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.
Example
See the example in the Syntax and Description section.
Default: The default value is 0 (zero), which limits memory use based on the value of the PGA_AGGREGATE_TARGET initialization parameter. When memory use approaches that value, loading of some partitions is delayed.
Purpose
The PARTITION_MEMORY parameter lets you limit the amount of memory used when you are loading many partitions. This parameter is helpful in situations in which the number of partitions you are loading use up large amounts of memory, perhaps even exceeding available memory (this can happen especially when the data is compressed).
Once the specified limit is reached, loading of some partition rows is delayed until memory use falls below the limit.
Syntax and Description
PARTITION_MEMORY=n
The parameter value n is in kilobytes.
If n is set to 0 (the default), then SQL*Loader uses a value that is a function of the PGA_AGGREGATE_TARGET initialization parameter.
If n is set to -1 (minus 1), then SQL*Loader makes no attempt use less memory when loading many partitions.
Restrictions
This parameter is only valid for direct path loads.
This parameter is available only in Oracle Database 12c Release 1 (12.1.0.2) and later.
Example
The following example limits memory use to 1 GB.
> sqlldr hr CONTROL=t.ctl DIRECT=true PARTITION_MEMORY=1000000
Default: 1048576
Purpose
The READSIZE parameter lets you specify (in bytes) the size of the read buffer, if you choose not to use the default.
Syntax and Description
READSIZE=n
In the conventional path method, the bind array is limited by the size of the read buffer. Therefore, the advantage of a larger read buffer is that more data can be read before a commit operation is required.
For example, setting READSIZE to 1000000 enables SQL*Loader to perform reads from the data file in chunks of 1,000,000 bytes before a commit is required.
Note:
If the READSIZE value specified is smaller than the BINDSIZE value, then the READSIZE value will be increased.
See "BINDSIZE".
Restrictions
Example
The following example sets the size of the read buffer to 500,000 bytes which means that commit operations will be required more often than if the default or a value larger than the default were used.
READSIZE=500000
Default: FALSE
Purpose
Syntax and Description
RESUMABLE=[TRUE | FALSE]
See Also:
Oracle Database Administrator's Guide for more information about resumable space allocation
Restrictions
Because this parameter is disabled by default, you must set RESUMABLE=TRUE to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.
Example
The following example enables resumable space allocation:
RESUMABLE=TRUE
Default: 'User USERNAME(USERID), Session SESSIONID, Instance INSTANCEID'
Purpose
The RESUMABLE_NAME parameter identifies a statement that is resumable.
Syntax and Description
RESUMABLE_NAME='text_string'
This value is a user-defined text string that is inserted in either the USER_RESUMABLE or DBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended.
Restrictions
This parameter is ignored unless the RESUMABLE parameter is set to TRUE to enable resumable space allocation.
Example
RESUMABLE_NAME='my resumable sql'
Default: 7200 seconds (2 hours)
Purpose
The RESUMABLE_TIMEOUT parameter specifies the time period, in seconds, during which an error must be fixed.
Syntax and Description
RESUMABLE_TIMEOUT=n
If the error is not fixed within the timeout period, then execution of the statement is terminated, without finishing.
Restrictions
This parameter is ignored unless the RESUMABLE parameter is set to TRUE to enable resumable space allocation.
Example
The following example specifies that errors must be fixed within ten minutes (600 seconds).
RESUMABLE_TIMEOUT=600
Default: Conventional path default is 64. Direct path default is all rows.
Purpose
For conventional path loads, the ROWS parameter specifies the number of rows in the bind array. For direct path loads, the ROWS parameter specifies the number of rows to read from the data file(s) before a data save.
Syntax and Description
ROWS=n
Conventional path loads only: The ROWS parameter specifies the number of rows in the bind array. The maximum number of rows is 65534. See "Bind Arrays and Conventional Path Loads".
Direct path loads only: The ROWS parameter identifies the number of rows you want to read from the data file before a data save. The default is to read all rows and save data once at the end of the load. See "Using Data Saves to Protect Against Data Loss". The actual number of rows loaded into a table on a save is approximately the value of ROWS minus the number of discarded and rejected records since the last save.
Note:
If you specify a low value for ROWS and then attempt to compress data using table compression, the compression ratio will probably be degraded. Oracle recommends that you either specify a high value or accept the default value when compressing data.
Restrictions
The ROWS parameter is ignored for direct path loads when data is loaded into an Index Organized Table (IOT) or into a table containing VARRAYs, XML columns, or LOBs. This means that the load still takes place, but no save points are done.
Example
In a conventional path load, the following example would result in an error because the specified value exceeds the allowable maximum of 65534 rows.
ROWS=65900
The SDF_PREFIX parameter lets you specify a directory prefix which will be added to file names of LOBFILEs and secondary data files (SDFs) that are opened as part of a load operation.
Default: There is no default.
Purpose
If SDF_PREFIX is specified, then the string value must be specified as well. There is no validation or verification of the string. The value of SDF_PREFIX is prepended to the filenames used for all LOBFILEs and SDFs opened during the load. If the resulting string is not the name of as valid file, then the attempt to open that file fails and an error is reported.
If SDF_PREFIX is not specified, then file names for LOBFILEs and SDFs are assumed to be relative to the current working directory. Using SDF_PREFIX allows those files names to be relative to a different directory.
Note:
TheSDF_PREFIX parameter can also be specified in the OPTIONS clause in the SQL Loader control file.Syntax
SDF_PREFIX=string
Quotation marks are only required around the string if it contains characters that would confuse the command line parser (for example, a space).
The file names that are built by prepending SDF_PREFIX to the file names found in the record are passed to the operating system to open the file. The prefix can be relative to the current working directory from which SQL*Loader is being executed or it can be the start of an absolute path.
Restrictions
The SDF_PREFIX parameter should not be used if the file specifications for the LOBFILEs or SDFs contain full file names.
Example
The following SQL*Loader command looks for LOB files in the lobdir subdirectory of the current directory
sqlldr control=picts.ctl log=picts.log sdf_prefix=lobdir/
Default: There is no default.
Purpose
The SILENT parameter suppresses some of the content that is written to the screen during a SQL*Loader operation.
Syntax and Description
SILENT=[HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL]
Use the appropriate values to suppress one or more of the following (if more than one option is specified, they must be separated by commas):
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" messages and the status messages for the load 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.
PARTITIONS - Disables writing the per-partition statistics to the log file during a direct load of a partitioned table.
ALL - Implements all of the suppression values: HEADER, FEEDBACK, ERRORS, DISCARDS, and PARTITIONS.
Example
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
Default: 0 (No records are skipped.)
Purpose
The SKIP parameter specifies the number of logical records from the beginning of the file that should not be loaded. This allows you to continue loads that have been interrupted for some reason, without loading records that have already been processed.
Syntax and Description
SKIP=n
The SKIP parameter can be used for all conventional loads, for single-table direct path loads, and for multiple-table direct path loads when the same number of records was loaded into each table. It cannot be used for multiple-table direct path loads when a different number of records was loaded into each table.
If a WHEN clause is also present and the load involves secondary data, then the secondary data is skipped only if the WHEN clause succeeds for the record in the primary data file.
See Also:
Restrictions
The SKIP parameter cannot be used for external table loads.
Example
The following example skips the first 500 logical records in the data file(s) before proceeding with the load:
SKIP=500
Default: FALSE
Purpose
The SKIP_INDEX_MAINTENANCE parameter specifies whether to stop index maintenance for direct path loads.
Syntax and Description
SKIP_INDEX_MAINTENANCE=[TRUE | FALSE]
If set to TRUE, this parameter causes the index partitions that would have had index keys added to them to instead be marked Index Unusable because the index segment is inconsistent with respect to the data it indexes. Index segments that are unaffected by the load retain the state they had before the load.
The SKIP_INDEX_MAINTENANCE parameter:
Applies to both local and global indexes
Can be used (with the PARALLEL parameter) to do parallel loads on an object that has indexes
Can be used (with the PARTITION parameter on the INTO TABLE clause) to do a single partition load to a table that has global indexes
Puts a list (in the SQL*Loader log file) of the indexes and index partitions that the load set to an Index Unusable state
Restrictions
The SKIP_INDEX_MAINTENANCE parameter does not apply to conventional path loads.
Indexes that are unique and marked Unusable are not allowed to skip index maintenance. This rule is enforced by DML operations, and enforced by the direct path load to be consistent with DML.
Example
The following example stops index maintenance from taking place during a direct path load operation:
SKIP_INDEX_MAINTENANCE=TRUE
Default: The value of the Oracle Database configuration parameter, SKIP_UNUSABLE_INDEXES, as specified in the initialization parameter file. The default database setting is TRUE.
Purpose
The SKIP_UNUSABLE_INDEXES parameter specifies whether to skip an index encountered in an Index Unusable state and continue the load operation.
Syntax and Description
SKIP_UNUSABLE_INDEXES=[TRUE | FALSE]
A value of TRUE for SKIP_UNUSABLE_INDEXES means that if an index in an Index Unusable state is encountered, it is skipped and the load operation continues. This allows SQL*Loader to load a table with indexes that are in an Unusable state prior to the beginning of the load. Indexes that are not in an Unusable state at load time will be maintained by SQL*Loader. Indexes that are in an Unusable state at load time will not be maintained but will remain in an Unusable state at load completion.
Both SQL*Loader and Oracle Database provide a SKIP_UNUSABLE_INDEXES parameter. The SQL*Loader SKIP_UNUSABLE_INDEXES parameter is specified at the SQL*Loader command line. The Oracle Database SKIP_UNUSABLE_INDEXES parameter is specified as a configuration parameter in the initialization parameter file. It is important to understand how they affect each other.
If you specify a value for SKIP_UNUSABLE_INDEXES at the SQL*Loader command line, then it overrides the value of the SKIP_UNUSABLE_INDEXES configuration parameter in the initialization parameter file.
If you do not specify a value for SKIP_UNUSABLE_INDEXES at the SQL*Loader command line, then SQL*Loader uses the Oracle Database setting for the SKIP_UNUSABLE_INDEXES configuration parameter, as specified in the initialization parameter file. If the initialization parameter file does not specify a setting for SKIP_UNUSABLE_INDEXES, then the default setting is TRUE.
The SKIP_UNUSABLE_INDEXES parameter applies to both conventional and direct path loads.
Restrictions
Indexes that are unique and marked Unusable are not allowed to skip index maintenance. This rule is enforced by DML operations, and enforced by the direct path load to be consistent with DML.
Example
If the Oracle Database initialization parameter had a value of SKIP_UNUSABLE_INDEXES=FALSE, then the following parameter on the SQL*Loader command line would override it. Therefore, if an index in an Index Unusable state is encountered, it is skipped and the load operation continues.
SKIP_UNUSABLE_INDEXES=TRUE
Default: 256000
Purpose
The STREAMSIZE parameter specifies the size (in bytes) of the data stream sent from the client to the server.
Syntax and Description
STREAMSIZE=n
The STREAMSIZE parameter specifies the size of the direct path stream buffer. The number of column array rows (specified with the COLUMNARRAYROWS parameter) determines the number of rows loaded before the stream buffer is built. The optimal values for these parameters vary, depending on the system, input data types, and Oracle column data types used. When you are using optimal values for your particular configuration, the elapsed time in the SQL*Loader log file should go down.
Restrictions
The STREAMSIZE parameter applies only to direct path loads.
The minimum value for STREAMSIZE is 65536. If a value lower than 65536 is specified, then 65536 is used instead.
Example
The following example specifies a direct path stream buffer size of 300,000 bytes.
STREAMSIZE=300000
Default: LDRTRIM
Purpose
The TRIM parameter specifies that spaces should be trimmed from the beginning of a text field, the end of a text field, or both. Spaces include blanks and other nonprinting characters such as tabs, line feeds, and carriage returns.
Syntax and Description
TRIM=[LRTRIM | NOTRIM | LTRIM | RTRIM | LDRTRIM]
The valid values for the TRIM parameter are as follows:
NOTRIM indicates that no characters will be trimmed from the field. This setting generally yields that fastest performance.
LRTRIM, LTRIM, and RTRIM are used to indicate that characters should be trimmed from the field. LRTRIM means that both leading and trailing spaces are trimmed. LTRIM means that leading spaces will be trimmed. RTRIM means trailing spaces are trimmed.
LDRTRIM is the same as NOTRIM except in the following cases:
If the field is not a delimited field, then spaces will be trimmed from the right.
If the field is a delimited field with OPTIONALLY ENCLOSED BY specified, and the optional enclosures are missing for a particular instance, then spaces will be trimmed from the left.
If trimming is specified for a field that is all spaces, then the field is set to NULL.
Restrictions
The TRIM parameter is valid only when the external table load method is used.
Example
The following example would result in a load operation for which no characters are trimmed from any fields:
TRIM=NOTRIM
Default: If it is omitted, then you are prompted for it. If only a slash is used, then USERID defaults to your operating system login
Purpose
Syntax and Description
USERID=[username | / | SYS]
Specify a user name. For security reasons, Oracle recommends that you specify only the user name on the command line. SQL*Loader then prompts you for a password.
If you do not specify the USERID parameter, then you are prompted for it. If only a slash is used, then USERID defaults to your operating system login.
If you connect as user SYS, then you must also specify AS SYSDBA in the connect string.
Restrictions
Because the string, AS SYSDBA, contains a blank, some operating systems may require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character, such as backslashes.
See your Oracle operating system-specific documentation for information about special and reserved characters on your system.
Example
The following example specifies a user name of hr. SQL*Loader then prompts for a password. Because it is the first and only parameter specified, you do not need to include the parameter name USERID:
> sqlldr hr Password:
See Also:
Oracle SQL*Loader provides the results of a SQL*Loader run immediately upon completion. In addition to recording the results in a log file, SQL*Loader may also report the outcome in a process exit code. This Oracle SQL*Loader functionality allows for checking the outcome of a SQL*Loader invocation from the command line or a script. Table 8-1 shows the exit codes for various results.
Table 8-1 Exit Codes for SQL*Loader
| Result | Exit Code |
|---|---|
|
All rows loaded successfully |
|
|
All or some rows rejected |
|
|
All or some rows discarded |
|
|
Discontinued load |
|
|
Command-line or syntax errors |
|
|
Oracle errors nonrecoverable for SQL*Loader |
|
|
Operating system errors (such as file open/close and malloc) |
|
For Linux and UNIX operating systems, the exit codes are as follows:
EX_SUCC 0 EX_FAIL 1 EX_WARN 2 EX_FTL 3
For Windows operating systems, the exit codes are as follows:
EX_SUCC 0 EX_FAIL 1 EX_WARN 2 EX_FTL 4
If SQL*Loader returns any exit code other than zero, then you should consult your system log files and SQL*Loader log files for more detailed diagnostic information.
In UNIX, you can check the exit code from the shell to determine the outcome of a load.