This section describes each available SQL*Loader command-line keyword.
BAD (bad file)
BAD specifies the name of the bad file created by SQL*Loader to store records that cause errors during insert or that are improperly formatted. If a filename is not specified, the name of the control file is used by default with the .BAD extension. This file has the same format as the input datafile, so it can be loaded by the same control file after updates or corrections are made.
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 size)
BINDSIZE specifies the maximum size (bytes) of the bind array. The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS. The bind array is discussed in Determining the Size of the Bind Array. The default value is 65536 bytes.
CONTROL (control file)
CONTROL specifies the name of the control file that describes how to load data. If a file extension or file type is not specified, it defaults to CTL. If the filename is omitted, SQL*Loader prompts you for it.
Note: If your control filename contains special characters, your operating system will require that they be preceded by an escape character. See your operating system documentation.
If your operating system uses backslashes in its file system paths, you need to keep the following in mind:
DATA specifies the name of the datafile containing the data to be loaded. If a filename is not specified, the name of the control file is used by default. If you do not specify a file extension or file type, the default is .DAT.
Note: If you specify a file processing option when loading data from the control file, a warning message will be issued.
DIRECT (data path)
DIRECT specifies the data path, that is, the load method to use, either conventional path or direct path. TRUE specifies a direct path load. FALSE specifies a conventional path load. The default is FALSE.
DISCARDFILE (file name)
DISCARDFILE specifies a discard file (optional) to be created by SQL*Loader to store records that are neither inserted into a table nor rejected. If a filename is not specified, it defaults to DSC.
This file has the same format as the input datafile, so it can be loaded by the same control file after appropriate updates or corrections are made.
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 is specified also in the control file, the command-line value overrides it.
DISCARDMAX specifies the number of discard records to allow before data loading is terminated. The default value is all discards are allowed. To stop on the first discarded record, specify one (1).
ERRORS (errors to allow)
ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value of the ERRORS parameter, SQL*Loader terminates the load. The default is 50. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.
On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.
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/or rejected rows filtered out of all tables.
In all cases, SQL*Loader writes erroneous records to the bad file.
FILE (file to load into)
FILE specifies the database file to allocate extents from. It is used only for parallel loads. By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention.
LOAD (records to load)
LOAD specifies the maximum number of logical records to load (after skipping the specified number of records). By default all records are loaded. No error occurs if fewer than the maximum number of records are found.
LOG (log file)
LOG specifies the log file that SQL*Loader will create to store logging information about the loading process. If a filename is not specified, the name of the control file is used by default with the default extension (LOG).
PARALLEL (parallel load)
PARALLEL specifies whether direct loads can operate in multiple concurrent sessions to load data into the same table.
PARFILE (parameter file)
PARFILE specifies the name of a file that contains commonly used command-line parameters. For example, the command line could read:
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.
READSIZE (read buffer)
The READSIZE parameter lets you specify (in bytes) the size of the read buffer. The default value is 65536 bytes; however, you can specify a read buffer of any size depending on your system.
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 is required.
For example:sqlldr scott/tiger control=ulcas1.ctl readsize=1000000
This example enables SQL*Loader to perform reads from the external datafile in chunks of 1000000 bytes before a commit is required.
Note: The default value for both the READSIZE and BINDSIZE parameters is 65536 bytes. If you have specified a BINDSIZE that is smaller than the size you specified for READSIZE, the BINDSIZE value will be automatically increased to the specified value of READSIZE.
If the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.
This parameter is not related in any way to the READBUFFERS keyword used with direct path loads.
ROWS (rows per commit)
Conventional path loads only: ROWS specifies the number of rows in the bind array. The default is 64.
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 read all rows and save data once at the end of the load.
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)
When SQL*Loader begins, a header message like the following appears on the screen and is placed in the log file:
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, for example:
Commit point reached - logical record count 20
SQL*Loader may also display data error messages like the following:
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 keywords to suppress one or more of the following:
HEADER Suppresses the SQL*Loader header messages that normally
appear on the screen. Header messages still appear in the log
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.
PARTITIONS This Oracle8i option for a direct load of a partitioned table
disables writing the per-partition statistics to the log file.
ALL Implements all of the suppression keywords: HEADER,
FEEDBACK, ERRORS, DISCARDS, and PARTITIONS.
SKIP (records to skip)
SKIP specifies the number of logical records from the beginning of the file that should not be loaded. By default, no records are skipped.
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.
USERID is used to provide your Oracle username/password. If it is omitted, you are prompted for it. If only a slash is used, USERID defaults to your operating system login. A Net8 database link can be used for a conventional path load into a remote database.
Oracle 8i Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 8i Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 8i Tutorial
Sql*loader Case Studies
Sql*loader Control File Reference
Sql*loader Command-line Reference
Sql*loader: Log File Reference
Sql*loader: Conventional And Direct Path Loads
Dbverify: Offline Database Verification Utility
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.