Master Note for SQL*Loader (Doc ID 1264730.1)
By khrobins on Nov 08, 2010
Concepts/DefinitionsOracle SQL*Loader loads data from external files into tables in a database. It has a robust data parsing engine that works with a variety of data formats in the datafile. It can load data locally or across a network connection. It is possible to load multiple datafiles, load data into multiple tables, manipulate data with SQL functions in the same load session.
For more pointers on features and limitations with Oracle SQL*Loader review the SQL*Loader section in the Oracle Utilities guide.
Setup / Configuration / SyntaxReview Note 160521.1 for general pointers on using the SQL*Loader utility.
In general, the SQL*Loader client utility loads data into a database at the same release level or higher. For example, a 9.2 client SQL*Loader utility can load data into a 10.2.0.4 database. However, the datafile must be written in a form that the target system can recognize. For example, if the source has a native, floating-point representation that use 16 bytes and the target database recognizes native, floating-point numbers that are 12 bytes, SQL*Loader will have issues with loading the data on the target database.
The SQL*Loader log file will record activity during the load and indicate any error messages encountered. The log file should be reviewed to ensure the load completed as intended.
See Note 236666.1 for common commands and options used with SQL*Loader.
Performance IssuesIf experiencing slow performance with SQL*Loader consider the tips provided in Note 28631.1.
Other Issues10g and 11g can contain corrupted data after a direct path load through SQL*Loader when a table includes an encrypted column. Some data will be correct, but a subset of the data in the encrypted column can become corrupted. Bug 5910542 addressed this problem and a fix is included with 10.2.0.5 and 220.127.116.11. The only workaround is to use conventional load with SQL*Loader.
Tracing can be difficult with SQL*Loader. Here is a suggested approach to make tracing a slow load automatically. Create an analysis table when logged into the database using the user that will run the SQL*Loader job. Also create an event driven SQLLDR trace trigger to capture 10046 performance tracing on any user running a SQL*Loader load. See Note 789194.1 for the detailed instructions on setting this up for analysis.
Note 160002.1 for more on scenarios that lead to this error message.
Many notes exist discussing common uses for SQL*Loader. Do a search on the MyOracle Support Knowledge Base using keywords "SQL*Loader how to". Some examples of "how to" notes are Note 796098.1 - loading data with commas included in text fields, Note 183105.1 - increasing commit frequency during the load, and Note 116237.1 - using SQL*Loader to delete certain rows in the data.